How to query: Select games in categories A, B and C

Question

I'd like to know how to effectively run a query such as:

select game from Game game 
inner join game.Categories cat
where cat.Name in ('A', 'B')

This gives me games with categories A or B. But I want games that the the A category and the B category. This is to be used in HQL (NHibernate Query Language), but I'd like also to know how to do this on SQL.

Answer 1

Here's the SQL (assuming T-SQL):

/* Defining my own schema... */
declare @games table
(
 GameID int, Game varchar(20)
)

declare @gameCategories table
(
 GameID int, Category char(1)
)

insert into @games values (1, 'Risk')
insert into @games values (2, 'Spades')
insert into @games values (3, 'Cribbage')

insert into @gameCategories values (1, 'B')
insert into @gameCategories values (2, 'C')
insert into @gameCategories values (3, 'C')
insert into @gameCategories values (3, 'B')

select g.Game
from @games g
 inner join @gameCategories b on g.GameID = b.GameID and b.Category = 'B'
 inner join @gameCategories c on g.GameID = c.GameID and c.Category = 'C'

Answer 2

Assuming MySQL:

SELECT  g.*
FROM    game g
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    categories c
        WHERE   c.name IN ('A', 'B')
                AND c.game = g.id
        LIMIT 1 OFFSET 1
        )

The OFFSET value should be the number of items in the IN list minus 1, that is if you query for ('A', 'B', 'C') then you should use OFFSET 2, etc.