CODEWITHSUNDEEP
mysqlsql
codingManiac
codingManiac

Reputation: 1700

How to query rows with one matching and one non-matching field

Given tblAllKeys(fldKey, fldCode) and tblModKeys(fldKey, fldCode) with data in tblAllKeys of...

fldCode   fldKey
dba       1
dba       2
dba       3
dba       4
cde       1
cde       2
cde       3

in tblModKeys ...

fldCode   fldKey
dba       1
dba       2
dba       3
dba       4
dba       5
dba       6
cde       1
cde       2
cde       3
cde       4

I want to return the the rows from tblModKeys where fldCode matches with tblAllKeys.fldCode but where tblModKeys.fldKey does not exist in tblAllKeys.fldCode. I am interested to know if there is a way to do this with a join specifically?

Upvotes: 0

Views: 43

Answers (2)

Barmar
Barmar

Reputation: 782717

SELECT DISTINCT mk.*
FROM tblModkeys mk
JOIN tblAllkeys ak1 ON mk.fldcode = ak1.fldcode -- fldcode exists in tblAllkeys
LEFT JOIN tblAllkeys ak2 ON mk.fldkey = ak2.fldkey
WHERE ak2.fldkey IS NULL -- fldkey does not exist in tblAllkeys

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271161

This is a bit tricky, but your sample data does not do it justice. You need an example of a row like fgh, 5 in the mod keys table that is not in the all keys table.

The idea is to use left outer join, but only on the fldcode column. That way, you can check if there is no match to this code. Then do an aggregation to look for a match on fldkey;

select mk.*
from tblModkeys mk left outer join
     tblAllkeys ak
     on mk.fldcode = ak.fldcode
group by mk.fldcode, mk.fldkey
having sum(ak.fldkey = mk.fldkey) = 0 and sum(ak.fldcode is null) = 0;

Upvotes: 2

Related Questions