subquery

melgra70 · #1 (**permalink**) 03-23-11, 07:25

I'm writing my first subquery within an outer query and can't work out what I'm doing wrong:

from the entry table (event_id, horse_id, place)
and the horse table (horse_id, horse_name)
I have to list the horse_id and horse_name for each horse that has been placed in the top 3 (ie, got place 1, 2 or 3) two or more times.

this is my subquery, which worked:

select H.horse_id, H.horse_name
from entry E, Horse H
where E.horse_id = H.horse_id
and E.place < ‘4’;

this is the subquery within the outer query, which didn't work:

select H.horse_id, H.horse_name
from horse H
where 2 <= (select count(*)
from entry E, horse H
where E.horse_id = H.horse_id
and E.place < ‘4’);

Can anyone spot my error, please?

it-iss.com · #2 (**permalink**) 03-23-11, 08:28

Try this:

Code:

SELECT h.horse_id,
       h.horse_name
FROM   horse h,
       entry e
WHERE  e.horse_id = h.horse_id
       AND e.place <= 3
GROUP  BY h.horse_id,
          h.horse_name
HAVING COUNT(1) > 1;

mike_bike_kite · #3 (**permalink**) 03-23-11, 08:38

Quote:

Originally Posted by melgra70

Can anyone spot my error, please?

The subquery you have doesn't join to the horse table outside the brackets but to the horse table within the brackets.

As it-iss said you also don't need a subquery.

His query is far more efficient than yours (and easier to read).

Why is the '4' in quotes?

melgra70 · #4 (**permalink**) 03-24-11, 07:01

Thanks, Ronan and Mike.
Your solution is great and works well, but this exercise requires that I show competency in subquery creation. Hence, I still need to manipulate my solution with the subquery added into an outer query. I've removed the '4'.

select H.horse_id, H.horse_name
from entry E, Horse H
where E.horse_id = H.horse_id
and E.place <=3;