mysql many to many relation query

Question

Hello,

Maybe I am just falling asleep (or not!), but how can you do this:

I have a table (many to many), let's say for example with fields key1 and key2, in which I want to select all the key1 that don't have a relation with certain key2. As an example, if I have the following:

k1_A --- k2_A 
k1_A --- k2_B
k1_B --- k2_C
k1_C --- k2_D
k1_D --- k2_A

I want all the key1 that don't have "k2_A", so I would expect as result: k1_B,k1_C.

Thanks, Cheers

Answer 1

SELECT key1 
FROM table 
WHERE key1 NOT IN
(
  SELECT key1
  FROM table
  WHERE key2 = 'k2_A'
);