CODEWITHSUNDEEP
mysqlcount
tomaytotomato
tomaytotomato

Reputation: 4028

MySQL show a row that appears more than once with different values?

For a table (Ingredients) like this:

+-----+-----+
|id   |fruit|
+-----+-----+
|  1  | or  |
|  1  | ban |
|  2  | or  |
|  2  | or  |
+-----+-----+

I want to have a query that should produce a result like this:

id 1 is associated with two types of fruits.

I do not care if multiple rows have the same data, I only care if rows with the same id have different fruits.

e.g. id 2 is only associated with one type of fruit in both rows, i do not care about that!!!

I have pseudo code for what I want to achieve but not any mysql

Select all id's
From the ingredients table
Group by id
If group count of id is greater than 1 and has different fruits then display

I have a funny feeling I might not be able to do this in Mysql and will have to use some PHP.

Thanks for your time

Upvotes: 1

Views: 6155

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Your pseudo code in SQL:

SELECT id
FROM ingredients
GROUP BY id
HAVING COUNT( DISTINCT fruit ) > 1

and if it's slow, you can try this version - MAX() and MIN() may be faster than COUNT(DISTINCT ) - although it's less readable:

SELECT id
FROM ingredients
GROUP BY id
HAVING MAX( fruit ) > MIN( fruit )

Upvotes: 6

Marco
Marco

Reputation: 57593

Try this

SELECT id
FROM your_table
GROUP BY id
HAVING COUNT(DISTINCT fruit) > 1

Upvotes: 3

Related Questions