MySQL – Showing All Values When at Least One Matches

MySQL

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`)
);

Best Answer

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.

Related Question