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
sabevAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Try
SELECT * FROM T1 WHERE ID NOT IN (SELECT ID FROM T2)

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Modified for Smith
SELECT * FROM T1 WHERE NAME = 'SMITH' 
AND ID NOT IN (SELECT ID FROM T2) 

Open in new window

0
 
nschaferCommented:
Try this:

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

Hope this helps,

Neal.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nschaferCommented:
Guess we all had the same idea at the same time :-)
0
 
Rajkumar GsSoftware EngineerCommented:
:)
0
 
rajvjaCommented:
SELECT * FROM T1
left join T2 on t1.Id = T2.ID
where T1.Name = 'Smith' AND t2.ID IS NULL
0
 
waltersnowslinarnoldCommented:
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

0
 
waltersnowslinarnoldCommented:
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

0
 
deightonCommented:
SELECT * FROM T1 WHERE T1.NAME = 'SMITH'
AND  NOT EXISTS  (SELECT NULL FROM T2 WHERE T1.ID = T2.ID)
0
 
sabevAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.