asked on

Simple SQL join to exclude records question.

I have two tables -

T1
T1.id(int)
T1.name(string)

T2
T2.id(int)

I want to select all records in table T1 where name = “Smith” but exclude all those records where t1.id = t2.id.

In other words, If T1.name=”Smith” has an ID of 222, and T2.ID = 222 exists, I do not want to return the record.

SELECT * FROM T1, T2 WHERE T1.NAME = “SMITH” AND T1.ID != T2.ID
Doesn’t work.

Thanks

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajkumar Gs

Modified for Smith

SELECT * FROM T1 WHERE NAME = 'SMITH' 
AND ID NOT IN (SELECT ID FROM T2)

Open in new window

nschafer

Try this:

select * from t1 where Name = 'Smith' and id not in (Select id from t2)

Hope this helps,

Neal.

nschafer

Guess we all had the same idea at the same time :-)

Rajkumar Gs

rajvja

SELECT * FROM T1
left join T2 on t1.Id = T2.ID
where T1.Name = 'Smith' AND t2.ID IS NULL

waltersnowslinarnold

You can also use the below sample, more optimized way

SELECT * FROM T1 INNER JOIN T2
ON t2.ID = t1.ID 
WHERE Name = 'Smith' AND t2.ID <> t1.ID

Open in new window

waltersnowslinarnold

Oops! @rajvja is correct, it should be LEFT OUTER JOIN

SELECT * FROM T1
left join T2 on t1.ID = T2.ID
where T1.Name = 'Smith' AND t2.ID IS NULL

Open in new window

deighton

SELECT * FROM T1 WHERE T1.NAME = 'SMITH'
AND NOT EXISTS (SELECT NULL FROM T2 WHERE T1.ID = T2.ID)

sabev

ASKER

Lots of good answers, but this one was first. Hard to decide really.

And, my apologies. There is something strange going on, this is the second time that I have accepted this answer. Hope it works this time.