Table schema question

  • shah.simmy

    SSC-Addicted

    Points: 426

    if two tabel has two same schema,but not primary - foreign relation,i want to identify matching record from both table.how can i do it,i cant use join thre

  • SQLback

    SSCrazy

    Points: 2897

    Can you post the ddl of the two tables. Check out the following link

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Lowell

    SSC Guru

    Points: 323463

    shah.simmy (2/7/2012)


    if two tabel has two same schema,but not primary - foreign relation,i want to identify matching record from both table.how can i do it,i cant use join thre

    a foreign key between tables is not required to do a join...

    if you know the column(s) that contains common values in the two tables, you join on those.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • SQLFanatic

    SSCommitted

    Points: 1640

    shah.simmy (2/7/2012)


    if two tabel has two same schema,but not primary - foreign relation,i want to identify matching record from both table.how can i do it,i cant use join thre

    You may use intersect operator. See example below:-

    CREATE TABLE #TEST1 (FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), ORDERTYPE VARCHAR(20))

    INSERT INTO #TEST1

    SELECT 'STEVE','BRADLEY','SHOES' UNION

    SELECT 'JANICE','BENNETT','SHIRT' UNION

    SELECT 'JOE','CRAMMER','WATCH'

    GO

    CREATE TABLE #TEST2 (FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), ORDERTYPE VARCHAR(20))

    INSERT INTO #TEST2

    SELECT 'HUGH','GRANT','PANTS' UNION

    SELECT 'JANICE','BENNETT','SHIRT' UNION

    SELECT 'JOE','CRAMMER','WATCH'

    /*Return all the customers that exists in both tables having same order*/

    SELECT * FROM #TEST1

    INTERSECT

    SELECT * FROM #TEST2

    --Cleanup of tables

    DROP TABLE #TEST1

    DROP TABLE #TEST2

    Hope this helps!

    ________________________________________________________________
    "The greatest ignorance is being proud of your learning"

  • Paul White

    SSC Guru

    Points: 150442

    Example simple join:

    DECLARE @Table1 AS TABLE (col1 integer NULL, col2 integer NULL);

    DECLARE @Table2 AS TABLE (col1 integer NULL, col2 integer NULL);

    INSERT @Table1 VALUES (1, 10), (2, 20), (3, 30), (4, 40);

    INSERT @Table2 VALUES (1, -1), (3, -3), (5, -5), (7, -7);

    SELECT *

    FROM @Table1 AS t1

    JOIN @Table2 AS t2 ON

    t2.col1 = t1.col1;

  • shah.simmy

    SSC-Addicted

    Points: 426

    Thanks guys ,you solve my pproblem

  • SQLFanatic

    SSCommitted

    Points: 1640

    Glad that your problem is solved.

    Please try to be as much descriptive as you can going forward. It's always great to provide some sample scripts (Create/Insert) to simulate the problem that you are facing.

    🙂

    ________________________________________________________________
    "The greatest ignorance is being proud of your learning"

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply