CODEWITHSUNDEEP
sqlt-sql
McGoo2000
McGoo2000

Reputation: 3

Find unmatched between 2 tables using field as selection criteria

I have the following tables as a example.

Table 1

EmpID LastName FirstName
1 Smith John
2 Jones Bob
3 Citizen Jane

Table 2

EmpID ReqID ReqDesc
1 1 Car
1 2 Diploma
1 3 Phone
2 1 Car
2 3 Phone
3 1 Car
3 2 Diploma
3 3 Phone

I want to return all records from Table 1 that don't have an entry in Table 2 that don't have a Diploma for example so ReqID = 2

This query gives me all records back though not just Bob Jones' record. As they have other records that ReqID <> 2. Is there some way to group them to achieve this?

SELECT Table1.EMPID,
FROM Table1 LEFT JOIN Table2 ON Table1.EMPID = Table2.EMPID
WHERE Table2.ReqID <> 2;

Upvotes: 0

Views: 32

Answers (2)

Popeye
Popeye

Reputation: 35900

Use exists:

SELECT t1.*,
  FROM Table1 t1
 WHERE NOT EXISTS 
      (select 1 from Table2 t2 
        where t1.EMPID = T2.EMPID and t2.reqdesc = 'Diploma');

Upvotes: 0

Martin Cairney
Martin Cairney

Reputation: 1767

SELECT Table1.EmpID
FROM Table1
WHERE NOT EXISTS  (SELECT 1 FROM Table2 WHERE Table2.EMPID = Table1.EmpID AND Table2.ReqID = 2)

Upvotes: 0

Related Questions