user2977151 user2977151 - 5 months ago 9
SQL Question

SQL inner join and on multiple rows

so, I am creating an music database.
I am using three tables (files, categories, categories_assignments).

I want to be able to select a file that is in multiple categories (e.g. a song that is both pop and rock)

I already have made the

or
variance (included below for reference)

SELECT DISTINCT `files`.`filename` FROM `files`
INNER JOIN `categories_assignments`
ON `files`.`id` = `categories_assignments`.`fileid`
INNER JOIN `categories`
ON `categories_assignments`.`catid` = `categories`.`id`
WHERE `categories`.`name` = 'rock' OR `categories`.`name`='pop';

Answer

This is a "set-within-sets" problem -- you are looking for songs that have a set of categories. I like to solve this using group by and having:

SELECT f.filename
FROM files f JOIN
     categories_assignments ca 
     ON f.id = ca.fileid JOIN
     categories c
     ON ca.catid = c.id
WHERE c.name IN ('rock', 'pop')
GROUP BY f.filename
HAVING COUNT(*) = 2;

Notes:

  • Table aliases make the query easier to write and to read.
  • I don't see a need to put backticks around every identifier. That just makes the query harder to read.
  • You should use IN instead of multiple OR comparisons.
  • If you are learning SQL, then SELECT DISTINCT is almost never useful. Learn to use GROUP BY first.
Comments