I can't figure out how to formulate a query to retrieve a certain set of
data. I have two tables in a many-to-many relationship with each other
via a linking table. I want to get a list of the records in the first
table which have no matching records of a certain value in the second
table (including those with no matching records at all).
In other words, given a table Foo with records having keys 1, 2, and 3,
a table Bar with records having keys "a" and "b", and the following
linking table:
FooKey Barkey
1 a
2 a
2 b
If I run a query to find records in the Foo table that do not have a
matching Bar record "b", I should retrieve Foo records 1 and 3 (since
record 1 only has a matching "a" record and record 3 has no matching
records at all). The following query gets me most of the way there but
fails to exclude Foo record 2 because even though that record matches
"b" it also matches "a", and the "a" row is thus included in the
results. I want the presence of a matching "b" record in a row to
exclude Foo record 1 from the query results entirely.
SELECT Foo.FooKey ,
Bar.BarKey
FROM Foo
LEFT JOIN Link
ON Foo.FooKey = Link.FooKey
LEFT JOIN Bar
ON Link.BarKey = Bar.BarKey
WHERE Bar.BarKey != 'b'
OR Bar.BarKey IS NULL;
+--------+--------+
| FooKey | BarKey |
+--------+--------+
| 1 | a |
| 2 | a |
| 3 | NULL |
+--------+--------+
3 rows in set (0.00 sec)
Any idea how I could do this?
-myk
---------------------------------------------------------------------
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
