Thanks for your reply,
Diana Soares wrote:
> > 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...
I really want manufacturers which have both green AND 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"
The problem (for me ;-) with your solution is that it results in more than
one row per manufacturer.
I have found a solution for my first question though. The trick was to join
car_color_links and colors multiple times, once for each color.
(IIRC once again, since I don't have access to MySQL from here)
SET @color1 = "Blue";
SET @color2 = "Green";
SELECT ca.Manufacturer, @color1, @color2
FROM cars ca,
car_color_links l1,
colors c1,
car_color_links l2,
colors c2
WHERE ca.ID = l1.car_ID
AND l1.color_ID = c1.id
AND c1.name = @color1
AND ca.ID = l2.car_ID
AND l2.color_id = c2.id
AND c2.name = @color2;
> > 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"
Hmm, does this only return one row for each Manufacturer? I'll have to check it out.
Well at least I know I can't use the same trick here as I did in my
first query, since I don't know how many colors I could get returned.
/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
