Pfthb

I have some horse data in a table and am practicing SQL while following my hobby. I would like to find an elegant way to solve this problem. Right now I have this convoluted way of getting the answer but I know there HAS to be an easier way.

Description:

I'll make it really simple (Assume these 8 rows are the entire table). I have a table with 4 columns. HORSE_ID, NAME, TRACK, Date

A horse might run at one track or many different tracks. The end goal is to find what only the horses who have ran/campaigned at only one specific track....in this case, I want to see the horses that have run all their races at SA (Santa Anita)

HORSE_ID NAME TRACK DATE 
1 JUSTIFY SA FEB-2018
2 JUSTIFY PIM MAY-2018
3 JUSTIFY BEL JUN-2018
4 KANTHAKA SA DEC-2017
5 KANTHAKA SA JAN-2018
7 THREE RULES GP JUL-2016
8 DABSTER SA JAN-2018 

So if I ran this query with this data, the only horses I would expect to see are KANTHAKA, and DABSTER because they are the only horses that only ran all their races at Santa Anita track. So say next month KANTHAKA ran at ANOTHER DIFFERENT track, then the next time the query was run, only DABSTER would show up.

Does this make sense?

Try using GROUP BY with HAVING:

SELECT NAME
FROM yourTable
GROUP BY NAME
HAVING MIN(TRACK) = MAX(TRACK);

Writing the HAVING clause as above is preferable to writing HAVING COUNT(DISTINCT TRACK) = 1. The reason for this is that the above query can make use of an index on (NAME, TRACK).

If in addition you wish to restrict to a single track, then we can try:

SELECT NAME
FROM yourTable
GROUP BY NAME
HAVING MIN(TRACK) = MAX(TRACK) AND MIN(TRACK) = 'SA';

You can do a subquery requesting the count of distinct tracks to be 1 using GROUP BY and HAVING COUNT DISTINCT and then select those WHERE the track is 'SA':

SELECT NAME
FROM 
 (SELECT NAME, MIN(TRACK) as TRACK
 FROM HORSES
 GROUP BY 1
 HAVING COUNT (DISTINCT TRACK) = 1) horses_one_race
WHERE 
 TRACK = 'SA'