MySQL return set if values don't exist

Question

Let's say I have 5 rows, containing the following:

ID     KEY       VALUE
1      address   123 Main Street
1      latlng    (111.139, 111.238)
2      address   45 South Street
3      address   67 North Ave
3      latlng    (448.002, 100.301110)

Now, if I wanted to return ONLY the row that did NOT contain a corresponding latlng row to accompany the address (ie, ID 2) how would I go about doing that with a mySQL statement?

This one has me stumped!!

I suggest that you make an attempt, then show us what you tried and what went wrong. — showdev, Dec 19 '13 at 18:15

score 1 · Answer 1 · answered Dec 19 '13 at 18:22

1

Try something like

SELECT t1.*
FROM Table t1
    LEFT OUTER JOIN Table t2 ON t1.id = t2.id
WHERE t1.Key = 'latlng' AND t2.Key = 'address' AND t2.id IS NULL

answered Dec 19 '13 at 18:22

user2989408

3,127
1
17
15

I couldn't get this to work at all. – f8xmulder Dec 19 '13 at 19:07

score 1 · Accepted Answer · answered Dec 19 '13 at 18:28

1

SELECT *
FROM TABLE
WHERE ID NOT IN
(SELECT ID FROM TABLE WHERE KEY = 'LATLNG')

answered Dec 19 '13 at 18:28

Santhosh

1,771
1
15
25

Here's the query I ran, based on your answer: SELECT * FROM `datta` WHERE `key` = 'address' AND `id` NOT IN (SELECT `id` FROM `datta` WHERE `key` = 'latlng') And I think that is going to do it! Thanks! – f8xmulder Dec 19 '13 at 19:08

MySQL return set if values don't exist

2 Answers2