Suppress repeating ordering values
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 |
+---------------+---------------------------+