I'm learning SQL, and I'm having trouble writing a query to find games that have multiple genres at the same time.

I have a table like this:

So how to write a query that returns list of action horror games?, I expect to get only Circle beat in this case

CodePudding user response：

select game
    from game_table
    where genre in ('action')
intersect 
select game
    from game_table
    where genre in ('horror'); 

CodePudding user response：

One way to achieve this, with a single pass of the table, is to use a having clause like so:

select
       game
from game_table
where genre in ('action','horror')
group by
       game
having (  max(case when genre ='action' then 1 else 0 end) 
          max(case when genre ='horror' then 1 else 0 end) ) = 2

note, a having clause can filter the results of an aggregation function such as max() (maximum) - but a where clause cannot do that. But, you can use both clauses in a single query as you can see above, the where clause reduces the total number of rows to be considered and the having clause considers some aggregated data to filter out rows that don't meet the wanted aggregated data conditions.