how to compare 2 table with missing identifiers

• jimm-792950

  SSC Enthusiast

  Points: 150

  More actions

  April 1, 2009 at 1:23 pm

  #129577

  I'm not even sure how to really title this question, but here goes...

  I have two table that have the same identifying number in them. But each table may or may not have a row to join to in the other table. Here's an example

  Table 1 has: 1, 3, 5, 6, 7, 9

  Table 2 has: 2, 4, 6, 7, 8, 9

  The output I need is:

  1, null

  null, 2

  3, null

  null, 4

  5, null

  6, 6

  7, 7

  null, 8

  9, 9

  I'm trying find out what is missing in both tables based on what is in the other table.

  Is there a way to do this?

  Thanks for any and all answers.

• jsanborn

  Old Hand

  Points: 348

  More actions

  April 1, 2009 at 1:31 pm

  #970182

  CREATE TABLE #one (id INT)

  CREATE TABLE #two (id INT)

  INSERT INTO #one VALUES (1)

  INSERT INTO #one VALUES (3)

  INSERT INTO #one VALUES (5)

  INSERT INTO #one VALUES (6)

  INSERT INTO #two VALUES (1)

  INSERT INTO #two VALUES (2)

  INSERT INTO #two VALUES (4)

  INSERT INTO #two VALUES (6)

  SELECT * FROM #one

  SELECT * FROM #two

  SELECT * FROM #one o FULL OUTER JOIN #two t

  ON o.id = t.id

  DROP TABLE #one

  DROP TABLE #two

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9119

  More actions

  April 1, 2009 at 1:38 pm

  #970185

  Hi

  As it stated in previous post you need to use FULL OUTER joing.

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers

  
  

• jimm-792950

  SSC Enthusiast

  Points: 150

  More actions

  April 1, 2009 at 1:46 pm

  #970187

  Thanks that worked.

  When I first tried it, it didn't do any different than a left join (on my tables). Your code worked.

  then I realized that I needed to "select *". Once I did that it worked.

  thanks,

  Jim