StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Advanced SELECT help
text
Body
copied!<p>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.</p> <p>I have 3 tables the first one we will call Shows, structured something like this:</p> <pre><code>+----+--------------------------+ | id | title | +----+--------------------------+ | 1 | Example #1 | | 2 | Example #2 | | 3 | Example #3 | +----+--------------------------+ </code></pre> <p>Plain and simple.</p> <p>My next table is called Categories, and lookes like this:</p> <pre><code>+----+--------------------------+ | id | category | +----+--------------------------+ | 1 | Comedy | | 2 | Drama | | 3 | Action | +----+--------------------------+ </code></pre> <p>And a final table called Show_categories:</p> <pre><code>+---------+---------+ | show_id | cat_id | +---------+---------+ | 1 | 1 | | 1 | 3 | | 2 | 2 | | 2 | 3 | | 3 | 1 | | 3 | 2 | +---------+---------+ </code></pre> <p>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.</p> <p>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.</p> <p>Example of a working single category search (Selecting all comedy shows):</p> <pre><code>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 </code></pre> <p>And a query that is impossible (because cat_id can't equal 2 different things):</p> <pre><code>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 </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload