CODEWITHSUNDEEP
sql-servert-sql
farnholdt
farnholdt

Reputation: 173

Check if table has specific row value

I have two tables called game and gameprogress. Gameprogress has a column called state that describes progress. Game is related to gameprogress trough an id.

Possible states is: 1,3,4 in gameprogress.

I want to find games without a state = 1 and without a state = 4. I've tried something like this: 

select top 10 gp.gameid, count(gp.state) as dup
from gameprogress gp 
join game g on g.id= gp.gameid
where g.gamestate != 2 and gp.state != 1
group by gp.gameid
having count(gp.state)>1

Upvotes: 1

Views: 173

Answers (1)

sagi
sagi

Reputation: 40471

You can use CASE EXPRESSION in the HAVING clause :

SELECT TOP 10 g.gameid
FROM game g
LEFT JOIN gameprogress gp 
 ON g.id= gp.gameid
GROUP BY g.gameid
HAVING COUNT(CASE WHEN gp.state = 3 THEN 1 END) = COUNT(*)

Upvotes: 1

Related Questions