0

Given three tables items, genres and itemGenres; how can I filter out the items that have at least the given value while also outputting the rest of its genres.

With this data

INSERT INTO items (title) VALUES ('Mad Max: Fury Road'), ('The Big Lebowski');
INSERT INTO genres (genre) VALUES ('action'), ('comedy'), ('post-apocalyptic');
INSERT INTO itemGenres (itemId, genreId) VALUES (1,1), (1,3), (2,2);

and this query

SELECT 
  items.*,
  GROUP_CONCAT(genres.id) as genreIds,
  GROUP_CONCAT(genres.genre) as genres FROM items
LEFT JOIN itemGenres ON
  items.id = itemGenres.itemId
LEFT JOIN genres ON
  genres.id = itemGenres.genreId
WHERE
  genres.id IN (1)
GROUP BY
  items.id

I get the following result

| id |              title | genreIds | genres |
|----|--------------------|----------|--------|
|  1 | Mad Max: Fury Road |        1 | action |

How do I get it to be this?

| id |              title | genreIds |                   genres |
|----|--------------------|----------|------------------------- |
|  1 | Mad Max: Fury Road |      1,3 | action, post-apocalyptic |

The tables are created like this

CREATE TABLE `items` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `itemGenres` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `itemId` int(1) unsigned DEFAULT NULL,
  `genreId` int(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `genres` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `genre` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);
3
  • Do you mean you want genreIDs 1,3? (you have 1,2 right now) – JasonBluefire Jun 24 '15 at 19:40
  • Yeah, just updated – Oskar Persson Jun 24 '15 at 19:40
  • See the LISTAGG function – David Balažic Jun 24 '15 at 19:46
1

You need 2 joins to itemGenres, separating the "check" logic (WHERE genres.id IN (1)) and the "gathering of all related genres":

SELECT 
  items.*,
  GROUP_CONCAT(genres.id ORDER BY genres.id)    AS genreIds,
  GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres 
FROM items
  LEFT JOIN itemGenres ON
    items.id = itemGenres.itemId
  LEFT JOIN genres ON
    genres.id = itemGenres.genreId
WHERE EXISTS
  ( SELECT *
    FROM itemGenres AS genre_check 
    WHERE genre_check.itemId = items.id
      AND genre_check.genreId IN (1)
  )
GROUP BY
  items.id ;

The LEFT joins can be replaced with INNER joins, the results would be identical.


Actually, it could be done with a change of your query, moving the check from WHERE to HAVING but the condition has to be modified:

SELECT 
  items.*,
  GROUP_CONCAT(genres.id ORDER BY genres.id)    AS genreIds,
  GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres 
FROM items
  JOIN itemGenres ON
    items.id = itemGenres.itemId
  JOIN genres ON
    genres.id = itemGenres.genreId
GROUP BY
  items.id 
HAVING
  COUNT(CASE WHEN itemGenres.genreId IN (1) THEN 1 END) > 0 ;

My personal preference would be the 1st solution but you can test in your database, which of the two is more efficient.

6
  • Could I somehow expand one of the above solutions to require all of the values in the IN clause? Example: IN (1,2) would return items that have genres 1, 2 and 3 but not items that only have genre 1 – Oskar Persson Jun 24 '15 at 21:02
  • I'd think that IN (1,2) would return items with either 1 or 2 or both. Have you tried it? – ypercubeᵀᴹ Jun 24 '15 at 21:03
  • Yes, that is what's happening. What I meant is that I want it to require both. But it could also be more than that. So one item with three genres (#1,#2,#3) which matches both #1 and #2 would be included but not one item with one genre (#1) which matches genre #1 but not genre #2. – Oskar Persson Jun 24 '15 at 21:12
  • Ah, you want the other way, ok. That is a "relational division" problem. It can be solved with many ways, some similar to these queries. – ypercubeᵀᴹ Jun 24 '15 at 21:13
  • 1
    Right, if the (genreId, itemId) combination is UNIQUE, then your query is correct. – ypercubeᵀᴹ Jun 24 '15 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.