How to query: Select games in categories A, B and C
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')
开发者_Python百科
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.
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.
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'
精彩评论