SQL: How to make a selection by category?

Question

There are two tables, categoriesand books, and I would like to select all the books based on these categories.

Category table:

cat_id | book_id
----------------
1      | 1
2      | 1
3      | 1
3      | 2

Table of books:

id  | name
----------------
1   | abc
2   | def

I tried SELECT * FROM categories WHERE cat_id IN(1,3), but then it returns books containing at least one of these categories. I would like it to return only books containing ALL categories, so it should return only all (or one) lines, where book_id = 1, since this is the only book with all the categories indicated.

Answer 1

Try:

select book_id
from categories
group by book_id
having sum( ( cat_id in (1,3) )::int ) = 2

postgres , (: http://fxjr.blogspot.com/2009/05/npgsql-tips-using-in-queries-with.html), :

select book_id
from categories
group by book_id
having sum( ( cat_id = ANY(ARRAY[1,3]) )::int ) = 2

:

select categories.book_id, books.name
from categories
join books on books.id = categories.book_id
group by categories.book_id
    ,books.name
having sum( ( categories.cat_id in (1,3) )::int ) = 2

@ , :

ANSI SQL:

select categories.book_id, books.name
from categories
join books on books.id = categories.book_id
group by categories.book_id
    ,books.name
having count(case when categories.cat_id in (1,3) then 1 end) = 2

:

select book_id
from categories
group by book_id
having count( case when cat_id in (1,3) then 1 end ) = 2

---

, (.. having) , where having:?...

select book_id
from categories
where category_id in (1,3)
group by book_id
having count(*) = 2

... , having, , 1 3 2 3 4. FTW! , , .

:

select book_id
from categories
group by book_id
having 
    count( case when cat_id in (1,3) then 1 end ) = 2 
    or count( case when cat_id in (2,3,4) then 1 end ) = 3

(, , , ), where where:

select book_id
from categories
where cat_id in (1,2,3,4)
group by book_id
having 
    count( case when cat_id in (1,3) then 1 end ) = 2 
    or count( case when cat_id in (2,3,4) then 1 end ) = 3

[EDIT]

, MySQL:

select book_id
from categories
group by book_id
having sum( cat_id in (1,3) ) = 2

Answer 2

. n n , n . , , n :

SELECT T.cat_id, count(*) hits FROM
(
    SELECT * FROM categories WHERE cat_id IN(1,3)
) T
GROUP BY T.cat_id
HAVING hits = 2

Answer 3

:

SELECT book_id FROM categories WHERE cat_id = 1 
INTERSECT 
SELECT book_id FROM categories WHERE cat_id = 3;

INTERSECT, .

Answer 4

, :

SELECT books.*
FROM books
     JOIN categories cat1 ON cat1.book_id = books.book_id
     JOIN categories cat3 ON cat3.book_id = books.book_id
WHERE cat1.cat_id = 1
      AND cat3.cat_id = 3

, WHERE EXISTS (semi join), .

Answer 5

SELECT * FROM 
(
 SELECT b.id, count(c.cat_id) as cat_count
 FROM books AS b
 JOIN cats AS c
   ON ( b.id = c.book_id )
 GROUP BY b.id
) AS t
WHERE t.cat_count = ( SELECT DISTINCT count(cat_id) FROM cat );

, . , , - .

Answer 6

:

SELECT * FROM books WHERE id IN 
(SELECT book_id
FROM categories
GROUP BY book_id 
HAVING COUNT(distinct cat_id)  = (select count(distinct cat_id) from categories))

Edited: I edited the request so that it returns books containing ALL categories as indicated in the question