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

Questions

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

July 5, 2022

+------------+-----------+-----------+-------------------------------+
| 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;

>Solution :

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.