SQL programming question - should be simple!

It shoudl be very straightforwards - I hope i'm missing something obvious!

I can only describe this by example:

I have a table:
ID --------- item
100--------- A
100--------- B
100--------- C
101----------A
101----------B
101----------C
102----------A
102----------B
103----------A
103----------B
103----------C
103----------D

pretty boring table. Still, it has a default item list (A, B, C). I need to be able to interrogate the table for those IDs whose items differ from this default (whether by missing some items, or having too many, or having separate ones). I need to be able to interrogate it in many ways.

So far I have tried along the lines:

SELECT ID
FROM TABLE
HAVING ID= ALL
(SELECT ID FROM TABLE WHERE item IN ('A','B','C'))

and

SELECT DISTINCT ID
FROM TABLE
WHERE ID =
(SELECT ID
FROM TABLE
WHERE item IN ('A','B','C')
GROUP BY ID
HAVING COUNT(DISTINCT item) = 3)

Am I even remotely on the right lines? Or do I need to create a view for this? If so, any suggestions. More than a little stuck... ta

Maybe combine your second stab at it with a left join somehow to get the ones that *don't* match.

My feeling was that the second stab wouldn't work at all because it would select, for example

104-----A
104-----E
104-----F

I.e. it has ONE OF ('A','B','C'), and it also has a total of 3 items.

Is this not the case? Is it clever enough to apply the 'distinct count = 3' to the preceding specified items?
(when I run the SQL I get some odd results).
Thanks...

I loved this question, as I just upgraded to MySQL 4.1, and this enabled me to test the subquery functionality they finally implemented.

This is the query you're looking for I think:



This returns all the id's that have other than the normal set of A, B and C (so more, less or different characters).

Good luck.