Hi, On Thu, 2003-02-20 at 13:20, Mac wrote: > 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.
I think that what you want is "OR" and not "AND". You want manufacturers which have green cars OR blue cars... > 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. 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 = "Green" OR co.Name = "Blue" (is it JOIN or "INNER JOIN" ?) > 2. Select which other colors of cars, a manufacturer which match > criteria 1 above, has. > i.e. Green AND Blue should return Black (Volvo), 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 <> "Green" AND co.Name <> "Blue" > could also return > Green, Blue and Black (Volvo) instead, if that is easier. 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) And then, maybe you can order results, or do this last query and add ca.Manufacturer = 'Volvo' in the WHERE..... > 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 -- Diana Soares --------------------------------------------------------------------- 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
