How To Select Id's That Have Rows For All Values From A Set

I want to select all Id from a table that have rows for both programs 'basketball' and 'football' Given a table like this: Id program 1 basketball 2 football 3 basketball 2

Solution 1:

Since you want to return the id's that have both values football and basketball, you can use the following to get the result:

select id
from yt
where program in ('basketball', 'football')
groupby id
havingcount(distinct program) =2;

See SQL Fiddle with Demo.

Since can also be done by joining on your table multiple times:

select t1.id
from yt t1
inner join yt t2
  on t1.id = t2.id
where t1.program = 'basketball'and t2.program = 'football';

See SQL Fiddle with Demo

Solution 2:

I think aggregation is the most generalizable approach for this:

select id
fromtablegroupby id
havingsum(casewhen program ='Football'then1else0end) >0andsum(casewhen program ='Basketball'then1else0end) >0

The sum() statement are counting the number of rows that have "football" and "basketball" respectively. When present, the number is greater than 0.

Solution 3:

You can do this with IN or OR syntax:

SELECT id
FROMtableWHERE program ='basketball'OR program ='football';

If you want to only get the first two results, add LIMIT 2 to the end.

By the way, it's really bad practice to have a table without a primary key, there is no way to index this table so performance would be very bad.