MySQL Advanced SELECT help

Question

Alright well I recently got into normalizing my database for this little side project that I have been creating for a while now, but I've just hit a brick wall. I'll try to give an understandable example of what I have and what I need to accomplish ― and hopefully it won't be too painful. OK.

I have 3 tables the first one we will call Shows, structured something like this:

+----+--------------------------+
| id | title                    |
+----+--------------------------+
| 1  | Example #1               |
| 2  | Example #2               |
| 3  | Example #3               |
+----+--------------------------+

Plain and simple.

My next table is called Categories, and lookes like this:

+----+--------------------------+
| id | category                 |
+----+--------------------------+
| 1  | Comedy                   |
| 2  | Drama                    |
| 3  | Action                   |
+----+--------------------------+

And a final table called Show_categories:

+---------+---------+
| show_id | cat_id  |
+---------+---------+
| 1       |       1 |
| 1       |       3 |
| 2       |       2 |
| 2       |       3 |
| 3       |       1 |
| 3       |       2 |
+---------+---------+

As you may have noticed the problem is the in my database a single show can have multiple categories. Everything is structured fine, except for the fact that I can't find a why to search for show with multiple categories.

If I were to search for action and comedy type shows I would be given Example #1, but it is not possible (at least with my queries), because the cat_id's inside the Show_categories are in different rows.

Example of a working single category search (Selecting all comedy shows):

SELECT s.id,s.title
  FROM Shows s JOIN Show_categories sc ON sc.anid=s.id
  WHERE sc.cat_id=1 GROUP BY s.id

And a query that is impossible (because cat_id can't equal 2 different things):

SELECT s.id,s.title
  FROM Shows s JOIN Show_categories sc ON sc.anid=s.id
  WHERE sc.cat_id=1 AND sc.cat_id=2 GROUP BY s.id

So to sum things up what I am asking is how do I handle a query where I am looking for a show based on multiple matching categories.

Answer 1

You need an OR statement.

 SELECT s.id,s.title
  FROM Shows s JOIN Show_categories sc ON sc.anid=s.id
  WHERE sc.cat_id=1 OR sc.cat_id=2 GROUP BY s.id

That is, you want all shows with either catid 1 OR catid 2. So this query will return 1, 2 and 3.

Answer 2

Use:

 SELECT s.id,
        s.title
    FROM SHOWS s 
    JOIN SHOW_CATEGORIES sc ON sc.anid = s.id
   WHERE sc.cat_id IN (1, 2) 
GROUP BY s.id, s.title
  HAVING COUNT(DISTINCT sc.cat_id) = 2

The COUNT(DISTINCT sc.cat_id) comparison needs to equal the number of cat_id values listed in the IN clause. But if both the SHOW_CATEGORIES show_id and cat_id columns are either the primary key, or there's a unique constraint on both columns -- then you can use COUNT(sc.cat_id).