Excluding results for some foreign keys only

[Blackguard]

I have a table like such
TABLE ids
id -- key
1 | 3
1 | 4
1 | 5
2 | 3
2 | 2
3 | 5

and one as such

TABLE names
id -- name
1 | bob
2 | jim
3 | joe

I'm trying to get all the names associated with one key but not any other. For example, I'd like to get the names associated with key 5 but not associated with any other key.

I'm trying to do it like this, but it doesn't work :

<pre>
SELECT names.name FROM names, ids WHERE names.id = ids.id AND ids.key <> 3 AND ids.key <> 4 AND ids.key = 5
</pre>

In this case, my hoped for result would be only joe. But it doesn't work like this apparently. Do I need to JOIN the table to itself?

[laserlight]

A subquery would be easy to understand:

SELECT name
FROM names, ids
WHERE names.id=ids.id AND ids."key"=5
    AND ids.id NOT IN (SELECT id FROM ids WHERE "key"<>5);

Note that the double quotes should be replaced by backticks with MySQL.

[Blackguard]

Thanks a lot, that will definitely do the trick!

[laserlight]

No problem 🙂
Remember to mark this thread as resolved (if it is) using the thread tools.