Check if table has specific row value

Question

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

sagi · Accepted Answer · 2016-11-07 08:42:16Z

1

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(*)

edited Nov 7, 2016 at 8:42

answered Nov 7, 2016 at 8:05

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

farnholdt Over a year ago

Looks like the right way to go. But i have an issue in not getting the games with state = 4, i've tried to:SELECT g.id FROM game g LEFT JOIN gameprogress gp ON g.id= gp.gameid where g.gamestate != 2 and gp.state != 4 GROUP BY g.id HAVING COUNT(CASE WHEN gp.state = 1 THEN 1 END) = 0

sagi Over a year ago

Why should it return games with state = 4? Should I guess the data? Edit the question and provide the data sample!

farnholdt Over a year ago

My fault. Edited the question.

sagi Over a year ago

@farnholdt So you want only those who has state = 4 or state = 2 ? Explain why so I'll know which filters to add

farnholdt Over a year ago

I only want those games which only has state = 3 and therefor not has any other states.

|

Collectives™ on Stack Overflow

Check if table has specific row value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related