How to print duplicate column one time while keeping other relevant information? MySQL-CodePudding

First Name	Last Name	Author ID	Books Written
Bobby	Ewing	101	How to Grow Tomatoes
Bobby	Ewing	101	Last Train to Clarksville
Bobby	Ewing	101	Escape from Gilligans Island
Bobby	Ewing	101	How to Grow Cucumbers
Red	Skelton	102	Mr. Smith Goes to Washington
Red	Skelton	102	How to Digitally Sign
Red	Skelton	102	Fixing Computers
Red	Skelton	102	Cubs Win!

In this scenario, I would like for 'Bobby Ewing 101' to only show up one time while the "Books Written" column will print out every distinct book value. So it should look like this if possible:

First Name	Last Name	Author ID	Books Written
Bobby	Ewing	101	How to Grow Tomatoes
			Last Train to Clarksville
			Escape from Gilligans Island
			How to Grow Cucumbers
Red	Skelton	102	Mr. Smith Goes to Washington
			How to Digitally Sign
			Fixing Computers
			Cubs Win!

Heads up. This is my current SELECT statement:

SELECT a.firstName AS 'First Name', a.lastName 'Last Name', a.authorID AS 'Author ID', b.title AS 'Books Written' 
FROM Author a, WrittenBy w, Book b 
WHERE b.ISBN = w.ISBN AND w.authorID = a.authorID;

CodePudding user response：

You can use aggregation over the "Books Written" field using MySQL GROUP_CONCAT function as follows:

SELECT a.firstName                          AS `First Name`, 
       a.lastName                           AS `Last Name`, 
       a.authorID                           AS `Author ID`, 
       GROUP_CONCAT(b.title SEPARATOR '\n') AS `Books Written`
FROM       Author a 
INNER JOIN WrittenBy w 
        ON w.authorID = a.authorID
INNER JOIN Book b
        ON b.ISBN = w.ISBN
GROUP BY a.firstName,
         a.lastName,
         a.authorID

Note: it's good practice to make JOIN operations explicit and use backticks for field names in MySQL.