CODEWITHSUNDEEP
mysql
Rbangs78
Rbangs78

Reputation: 1

mysql subquery where value appears twice or more

I have two tables as below (MYSQL)

ENTRY table 

Event_id |  Horse_id |  Place  
101 101 1  
101 102 2  
101 201 3  
101 301 4  
102 201 2  
103 201 3  
201 101 1  
301 301 2  
401 102 7

HORSE table

Horse_id    Name  
101   Flash  
102   Star  
201   Boxer  
301   Daisy  
401   Snowy

I need to list the horse_id and name of horses that place (1, 2 or 3) two times or more. best I can come up with which is not near it I know (need to do a subquery)

select horse_tbl.horse_id, horse_tbl.name
from horse_tbl
left join entry_tbl 
on horse_tbl.horse_id = entry_tbl.horse_id
where place<4(
select count(horse_id)>1));

Any help would be greatly appreciated

Upvotes: 0

Views: 79

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

Your query is on the right track, but instead of using a WHERE clause to check the place, you should be using GROUP BY along with conditional aggregation in the HAVING clause:

SELECT
    h.horse_id,
    h.name
FROM horse_tbl h
LEFT JOIN entry_tbl e
    ON h.horse_id = e.horse_id
GROUP BY
    h.horse_id,
    h.name
HAVING SUM(CASE WHEN e.place IN (1, 2, 3) THEN 1 ELSE 0 END) >= 2

Output:

enter image description here

Demo here:

Rextester

Upvotes: 1

Related Questions