Tomer Tomer - 2 months ago 7
MySQL Question

MySQL Query with condition that is based on several rows

I have a table with measurements of stars.
Each row (i.e. a measurement) contains the star's ID and its Period.

Lets say for example:

Star_ID Period
1 4.1
1 3.6
2 4.1
2 4.1
2 8.7
3 2.5
4 1.3
4 1.3


I want to write a query that returns the Star_ID's only of the stars which have more then one measurement with the same period. In our example, it should return {2,4}.

Can anyone help me with that?

Answer

Here is another option which just does a GROUP BY only on the Star_ID column:

SELECT Star_ID
FROM yourTable
GROUP BY Star_ID
HAVING COUNT(*) - COUNT(DISTINCT Period) >= 1

Follow the link below for a running demo:

SQLFiddle

Comments