Post reply

Help returning results of 2 tables with matching records and records that don't match

  • September 19, 2012 at 11:04 am

    #267025

    Hi,

    I have two tables I'm interested in. The columns of interest are Main_Account (Table_A) and Primary_Account (Table_B)

    Now there are 3 scenarios:

    Records are in both A and B

    Records are in A and NOT in B

    Records are in B and NOT in A

    These Main_Accounts and Primary_Accounts are not unique in there respective tables as in one Main_Account/Primary_Account will have multiple NameID's

    I would need to select DISTINCT Main_Account and DISTINCT Primary_Account from these table, which record is selected is not important.

    There is an additional clause on Table A ie where IncidentDate > '2009-02-20'

    A simple Example would be:

    Table A : X, Y

    Table B : X, Z

    RESULT:

    A.........B

    X.........X

    Y.........NULL

    NULL.....Z

    Here is some sample data to better explain:

    --DROP TABLE #Table_A

    CREATE TABLE #Table_A (Main_Account INT,NameID int, IncidentDate Date )

    INSERT INTO #Table_A VALUES (4370,375454,'2008-09-01')

    INSERT INTO #Table_A VALUES (4370,646632,'2009-02-20')

    INSERT INTO #Table_A VALUES (41727,55667,'2009-12-11')

    INSERT INTO #Table_A VALUES (206308,77885,'2011-03-31')

    INSERT INTO #Table_A VALUES (206308,46774,'2009-08-02')

    INSERT INTO #Table_A VALUES (37430,97565,'2009-11-11')

    select * from #Table_A

    --DROP TABLE #Table_B

    CREATE TABLE #Table_B (Primary_Account INT,NameID int )

    INSERT INTO #Table_B VALUES (4370,45467)

    INSERT INTO #Table_B VALUES (4370,964085)

    INSERT INTO #Table_B VALUES (8888,47789906)

    INSERT INTO #Table_B VALUES (8888,964085)

    INSERT INTO #Table_B VALUES (41727,566545)

    INSERT INTO #Table_B VALUES (3535,580627)

    select * from #Table_B

    --DROP TABLE #Table_Result

    CREATE TABLE #Table_Result (MainAccount INT,Primary_Account int)

    INSERT INTO #Table_Result VALUES (4370,4370)

    INSERT INTO #Table_Result VALUES (41727,41727)

    INSERT INTO #Table_Result VALUES (206308,null)

    INSERT INTO #Table_Result VALUES (37430,null)

    INSERT INTO #Table_Result VALUES (null,8888)

    INSERT INTO #Table_Result VALUES (null,3535)

    select * from #Table_Result

    Thanks a million!!!!

  • September 19, 2012 at 11:23 am

    #1539716

    Hi there,

    I believe a full join would do the trick?

    SELECT DISTINCT Main_Account, Primary_Account

    FROM #Table_A a FULL JOIN #Table_B b

    ON a.Main_Account = b.Primary_Account

    Just a note on your IncidentDate...if you specify it in your where clause, it means that the record must at least exist in Table_A and thus your results wont be the same as when you dont check for the IncidentDate value.

    hope this helps

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

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