MySQL; get all cards & all decks associated for each cards in one request

Question

I want to be able to see each decks where a card is and display it as following. A card can be in multiple decks. And there will be many cards/decks. The server is in php.

|List of cards|   In decks        |
|-------------|-------------------|
|CardA        |DeckA, DeckC       |
|CardB        |DeckC, DeckF, DeckY|
|CardC        |                   |

The database as is:

CREATE TABLE `card` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL
);

CREATE TABLE `deck` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL
);
CREATE TABLE `deck_card` (
  `deck_id` INT NOT NULL ,
  `card_id` INT NOT NULL ,
  PRIMARY KEY (deck_id, card_id),
  FOREIGN KEY (card_id) REFERENCES card(id),
  FOREIGN KEY (deck_id) REFERENCES deck(id)
);

I want to avoid having to do a request for each card. Is there a way to request all the cards and each associated deck in one request using MySQL?

Answer 1

The next simple query can solve your problem:

SELECT 
    `c`.`name` `card_name`,
    COALESCE(GROUP_CONCAT(`d`.`name`),'') `deck_names`
FROM `card` `c`
LEFT JOIN `deck_card` `dc` ON `c`.`id` = `dc`.`card_id`
LEFT JOIN `deck` `d` ON `d`.`id` = `dc`.`deck_id`
GROUP BY `c`.`name`
;

Result from SQLize.online

+===========+=============+
| card_name | deck_names  |
+===========+=============+
| CardA     | DeckA,DeckB |
+-----------+-------------+
| CardB     | DeckC,DeckE |
+-----------+-------------+
| CardC     |             |
+-----------+-------------+