join

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  June 5, 2009 at 9:55 am

  #134837

  Table A

  Col1 Col2 Col3

  a a a

  b b b

  c c null

  d c a

  Table B

  Col1 Col2 Col3

  a a a

  b b b

  c c null

  d d d

  I would like to have all collumns of Table A not in Table B.

  I tried left join on A but i am getting even the 3rd record which has null as null can not be comapared. how do i get rid of null as they are same on both tables

  Expected Result Set = d c a

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 5, 2009 at 10:21 am

  #1005412

  Hi Tara

  In SQL Server 2005 the most simple way to handle this would be EXCEPT:

  DECLARE @a TABLE (Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))

  DECLARE @b-2 TABLE (Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))

  INSERT INTO @a

  SELECT 'a', 'a', 'a'

  UNION ALL SELECT 'b', 'b', 'b'

  UNION ALL SELECT 'c', 'c', null

  UNION ALL SELECT 'd', 'c', 'a'

  INSERT INTO @b-2

  SELECT 'a', 'a', 'a'

  UNION ALL SELECT 'b', 'b', 'b'

  UNION ALL SELECT 'c', 'c', null

  UNION ALL SELECT 'd', 'd', 'd'

  SELECT * FROM @a

  EXCEPT

  SELECT * FROM @b-2

  Greets

  Flo