SQLTeam.com | Weblogs | Forums

SQL Server 2014 - same userid value present for multiple program instances

tsql
sql2014

#1

Hi,

i've a table as defined below

i want to select users who have all these 3 programs (tennis, football, cricket). So when i run the query against the above table only user1 should be in the result. i tried the below query but it doesn't seem to work. Can someone please help out

select userid from table where (program= 'tennis' and program= 'football' and program= 'cricket')

Thanks


#2

can you try this
SELECT userid
FROM users u1
WHERE program='cricket' or program='football' or program='tennis'
GROUP BY userid
HAVING count(DISTINCT program) = 3


#3

That seems to be working. Thanks a lot for your help.