Hi,
I am using MySQL v3.23 and have a many-to-many relationship (cars <->
colors) that I have broken up into three tables as follows
cars:
ID Manufacturer
1 Volvo
2 BMW
3 Toyota
colors:
ID Name
1 Red
2 Green
3 Blue
4 Black
car_color_links:
car_ID color_ID
1 3
1 4
2 1
2 3
1 2
Selecting manufacturers from just one color with join works like a charm.
(IIRC, untested code:)
SELECT DISTINCT ca.Manufacturer, co.Name FROM car_color_links as l
JOIN colors as co ON (co.ID = l.color_ID)
JOIN cars as ca ON (ca.ID = l.car_ID)
WHERE co.Name = "Red"
But I can't (understandably) just add an AND to the WHERE statement to select two
colors.
What I want to do is:
1. Select all manufacturer which have Green AND Blue cars (or Green
AND Blue AND Black cars, and so on).
i.e. Green AND Blue should return Volvo.
2. Select which other colors of cars, a manufacturer which match
criteria 1 above, has.
i.e. Green AND Blue should return Black (Volvo), could also return
Green, Blue and Black (Volvo) instead, if that is easier.
Perhaps this is not possible in single queries, but at least I would
like to be able to do it with just MySQL commands (so I can put them
in a command file).
I have googled around but have not found any answers. The mysql
website mentions UNION, but as I said I am using MySQL v3.23.
Could anyone point me in the right direction?
/mac
--------------------------------------
Protect yourself from spam,
use http://sneakemail.com
---------------------------------------------------------------------
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
