FlimFlam FlimFlam - 6 months ago 33
MySQL Question

Query with subquery and having count(distinct) condition

I'm attempting to create a query that lists the name and id of every horse which has finished in the top 3 in an event 2 or more times.

These are the two tables which I'm using: enter image description here

enter image description here

And this is the query I've come up with:

SELECT horse.horse_id, horse.name FROM horse
INNER JOIN
(SELECT horse_id
FROM entry
WHERE place in ('1', '2', '3')
HAVING count(distinct place) >1)
entry on horse.horse_id=entry.horse_id;


I've clearly done something wrong, because when I run this query only flash comes up, when it should be flash and boxer.

Answer

You condition counts the number of distinct places a horse finished, which is wrong, as you'd definitely like to include a horse which finished first twice. Moreover, you're missing a group by clause:

SELECT     horse.horse_id, horse.name 
FROM       horse
INNER JOIN (SELECT   horse_id 
            FROM     entry 
            WHERE    place IN (1, 2, 3) -- should probably be numbers, BTW
            GROUP BY horse_id
            HAVING   COUNT(*) > 1) entry ON horse.horse_id = entry.horse_id;