Post reply

Table schema question

  • February 7, 2012 at 7:14 pm

    #254974

    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

  • February 7, 2012 at 7:29 pm

    #1444259

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

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

  • February 7, 2012 at 7:34 pm

    #1444260

    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!

  • February 7, 2012 at 9:21 pm

    #1444272

    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"

  • February 7, 2012 at 11:15 pm

    #1444289

    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;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 8, 2012 at 11:05 am

    #1444680

    Thanks guys ,you solve my pproblem

  • February 8, 2012 at 12:56 pm

    #1444734

    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 6 (of 6 total)

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