SQL query help

[pdpham]

I have an Access database. It has a table Candy which has 3 columns Name,
Color and Taste.

I want to write a query that select all Candy.Name that have same Color but
different Taste.

Name Color Taste
------ ------ ------
Fred Gray 1
Paul Green 2
John Green 3
Matt Blue 1
Jack Red 0
Jim Blue 5
.....
.....

Result:
Paul Green 2
John Green 3
Matt Blue 1
Jim Blue 5



Thanks

 

[John Spencer MVP]

PERHAPS the following will work for you. It detects colors that have at least
two different taste values. It then return all records that have that color


SELECT *
FROM SomeTable
WHERE Color in (
SELECT Color
FROM SomeTable
GROUP BY Color
HAVING Max(Taste) <> Min(Taste))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

So John's solution, which is what I was considering until I thought of
asking my question, will not work. But part of his solution is still
relevant.
Try this:

SELECT [Name],c.Color,c.Taste
FROM Candy as c INNER JOIN (
SELECT Color,Taste
FROM Candy
GROUP BY Color,Taste
HAVING Count(*) = 1) as q
ON c.color=q.color and c.taste=q.taste
WHERE Color in (
SELECT Color
FROM SomeTable
GROUP BY Color
HAVING Max(Taste) <> Min(Taste))

 

[John Spencer MVP]

Well I think that this could be a very complex question.

Do you want the record(s) where the taste differs from the majority selection.

If Color Green has 3 records with taste 1 and 1 record with taste 2 and
another with taste 3, you apparently want the records color Green and taste =
2 or 3.

How about Green records with taste values of 1 1 1 2 2 2 3?
Or Green records with taste values 1 2 3 4 5?
Or Green records with taste values 1 1 1 2 2 3?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

So John's solution, which is what I was considering until I thought of
asking my question, will not work. But part of his solution is still
relevant.
Try this:

SELECT [Name],c.Color,c.Taste
FROM Candy as c INNER JOIN (
SELECT Color,Taste
FROM Candy
GROUP BY Color,Taste
HAVING Count(*) = 1) as q
ON c.color=q.color and c.taste=q.taste
WHERE Color in (
SELECT Color
FROM SomeTable
GROUP BY Color
HAVING Max(Taste) <> Min(Taste))

Bob,
Here is what I'm really looking for:

Name Color Taste
------ ------ ------
Fred Gray 1
Paul Green 2
John Green 3
Matt Blue 1
Jack Red 0
Jim Blue 5
Larry Green 2
Reid1 Green 2
Reid2 Green 2
Reid3 Green 2
Reid4 Green 2
Reid5 Green 2
Reid6 Green 2
Matt1 Blue 1
Matt2 Blue 1
Matt3 Blue 1
Matt4 Blue 1
....
....

Result: ( I'm looking for: other Green candies have Taste 2, John
should have Taste 2....)

John Green 3
Jim Blue 5

Thanks for your help