* Jerry Rehak
> I have a table with the columns names and id. I want to be able
> to find all
> names with an id of '03' that do not have other records with id values of
> '10','20' or '37'.
>
> Is this even possible to do?
>
> name id
> a 03
> a 11
> a 12
> a 13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
> b 03
> b 10 I don't want 'b' because it has a 10
> c 04
> c 11
> c 20 I don't want 'c' because it doesn't have a 03
> d 03 I want 'd' because it has a 03 and no other records
This can be done with a simple LEFT JOIN:
SELECT t1.*
FROM table AS t1
LEFT JOIN table AS t2 ON
t2.name=t1.name AND
t2.id IN ('10','20','37')
WHERE t1.id='03' AND t2.id IS NULL;
We select the rows we want from t1, left join with the rows we don't want
(t2), and put as a condition in the where clause that we only want rows
where t2 was not found.
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
