CODEWITHSUNDEEP
sqlsql-server
draxex
draxex

Reputation: 79

How to query multiple conditions for one column in SQL?

I have three tables, one is book, the other one is category and last one book_category which has relation between book and category.

In my situation I have books and their categories. For example one book can have multiple categories. How can query the books have both categories.

For example, one book have novel and story categories, and other book have just story categories. How can I query only which have novel and story categories together.

This query returns books with their categories.

select b.name, c.name
from book as b,
     category as c,
     book_category as bc
where b.id = bc.place_id
and   c.id = bc.category_id

Upvotes: 1

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use aggregation:

select b.name
from book b join
     book_category bc
     on b.id = b.book_id join
     category c
     on c.id = bc.category_id
where c.category in ('story', 'novel')
group by b.name
having count(*) = 2;

Notes:

  • Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.
  • All JOINs should immediately be followed by ON.
  • You don't need to return the categories, because you know what they are.
  • The join using place_id does not look correct.
  • This assumes that book_categories does not have duplicates.

Upvotes: 1

Related Questions