Linking two tables to find a record only if there isn't a record in another table

Question

I have two tables...

tbl1
ClientNo        ApptID          Status
1234            1               121
1234            2               121
1235            1               121
1235            2               121


tbl2
ClientNo        Valid         
1234            17

I'm looking to select all clients from tbl1 - where ApptID = 2, and there is no corresponding record in tbl2. So based on this data, 1234 has a record in tbl2, then no need to display it in end result.

I would love to see this as the end result:

ClientNo        ApptID          Status
1235            2               121

score 1 · Accepted Answer · answered Apr 05 '17 at 13:28

1

You can try the following query:

SELECT t1.*
FROM tbl1 AS t1
WHERE t1.ApptID = 2 AND  
      NOT EXISTS (SELECT 1
                  FROM tbl2 AS t2
                  WHERE t1.ClientNo = t2.ClientNo )

answered Apr 05 '17 at 13:28

Giorgos Betsos

71,379
9
63
98

Yours was first that did it. Thank you. – BobSki Apr 05 '17 at 13:52

MaxZoom · Answer 2 · 2017-04-05T13:43:49.403

1

You need to compare the join field to NULL

SELECT t1.* FROM tbl1 t1
   LEFT JOIN tbl_2 t2
   ON t1.ClientNo = t2.ClientNo 
   WHERE t1.ApptID=2 AND t2.ClientNo IS NULL

Or use sub-query as below:

SELECT * FROM tbl1
   WHERE ApptID=2 AND ClientNo NOT IN (SELECT ClientNo FROM tbl_2)

edited Apr 05 '17 at 13:43

answered Apr 05 '17 at 13:30

MaxZoom

7,619
5
28
44

Get a syntax error near where' Incorrect syntax near the keyword 'WHERE'.' – BobSki Apr 05 '17 at 13:34
I have updated the answer and added one more approach – MaxZoom Apr 05 '17 at 13:44

score 1 · Answer 3 · answered Apr 05 '17 at 13:36

1

Please try the below answer:

SELECT * FROM TBL2 T2
RIGHT JOIN
(SELECT * FROM TBL1 WHERE ApptID = 2) TBL T1
ON T2.ClientNo = T1.ClientNo 
WHERE T2.ClientNo IS NOT NULL;

answered Apr 05 '17 at 13:36

Biswabid

1,378
11
26

score 0 · Answer 4 · edited May 23 '17 at 10:30

This question was asked and answered a lot of times:

You need to join with a first condition matching and a second condition which specifies the failed match:

SELECT t1.ClientNo
  FROM tbl1 t1
  LEFT JOIN tbl2 t2 ON
    t1.ClientNo = t2.ClientNo
    AND t1.ClientNo IS NULL

Linking two tables to find a record only if there isn't a record in another table

4 Answers4