Artful Common Queries

Suppress repeating ordering values

from the Artful Common Queries page

You have tables tracking authors and their books, for example:

CREATE TABLE author (
  id int(4) NOT NULL auto_increment PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO author (id, name) 
VALUES (1,'Brad Phillips'),(2,'Don Charles'),(3,'Kur Silver');
CREATE TABLE book (
  id int(4) NOT NULL auto_increment PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO book (id, name) 
VALUES (1,'MySQL in a bucket '),(2,'Databases for Delinquents'),
       (3,'Design Patterns'),(4,'PHP Professional'),(5,'Java Script Programming');
CREATE TABLE book_author (
  book_id int(4) NOT NULL default '0',
  author_id int(4) NOT NULL default '0'
);
INSERT INTO book_author (book_id, author_id) 
VALUES (1,1), (1,2), (2,3), (4,1), (3,1), (5,2);

You want to list authors' books while suppressing repeating authors' names. A simple solution is to use MySQL's extremely useful GROUP_CONCAT() function to group books by author:

SELECT 
  a.name AS Author,
  GROUP_CONCAT(b.name ORDER BY b.name) AS Books
FROM book_author AS ba
JOIN book AS b ON ba.book_id=b.id
JOIN author AS a ON ba.author_id=a.id
GROUP BY a.name;

For a neater-looking result: 1. Retrieve authors and their books. 2. Order them 3. Use a variable to remember and suppress repeating author names:

SET @last='';
SELECT 
  IF(r.author=@last,'',@last:=r.author) AS Author,
  r.book AS Book
FROM (
  SELECT DISTINCT a.name AS author,b.name AS book
  FROM book_author AS ba
  JOIN book AS b ON ba.book_id=b.id
  JOIN author AS a ON ba.author_id=a.id
  ORDER BY a.name,b.name
) AS r;
+---------------+---------------------------+
| author        | book                      |
+---------------+---------------------------+
| Brad Phillips | Design Patterns           |
|               | MySQL in a bucket         |
|               | PHP Professional          |
| Don Charles   | Java Script Programming   |
|               | MySQL in a bucket         |
| Kur Silver    | Databases for Delinquents |
+---------------+---------------------------+

Last updated 22 May 2024