NOT EXISTS on 2 elements 1

[JayKusch]

OK ... my brain has given up for the day - lol.

I have 2 tables:

Tbl1 and Tbl2 both have Col1 VarChar(10), Col2 VarChar(10)

I need to find what records in Tbl1 do not exist in Table 2 based on BOTH Col1 AND Col2.

I tried a couple of variations like:

select	* 
from	Tbl1
WHERE	NOT EXISTS (
		SELECT	*
		FROM	Tbl1 as t,
			Tbl2as t2
		WHERE	(t.Col1	= t2.Col1
		AND	 t.Col2 = t2.Col2 )

Obviously I am off ... and out of coffee and brain cells.


Thanks

J. Kusch

 

[gmmastros]

Take a look at this example.

Declare @tbl1 Table(Col1 VarChar(10), Col2 varchar(10))
Declare @tbl2 Table(Col1 VarChar(10), Col2 varchar(10))

Insert into @tbl1 Values('1234', 'abc')
Insert into @tbl1 Values('5678', 'xyz')
Insert into @tbl1 Values('5678', 'pdq')

Insert into @tbl2 Values('1234', 'abc')
Insert into @tbl2 Values('5678', 'xyz')

Select tbl1.*
From   @tbl1 As tbl1
       Left Join @tbl2 as tbl2
         On  tbl1.col1 = tbl2.col1
         and tbl1.col2 = tbl2.col2
Where  tbl2.col1 is null and tbl2.col2 is null

Notice the where clause checks for both columns (in tbl2) to be NULL. Depending on your table structure, you may only need to check for one of these.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JayKusch]

PERFECT!

Thanks

Thanks

J. Kusch