SQL Select from main table - detail table (formatted data)

Question

I have two tables named "Supplier" and "Contact".

The data in the contact table corresponds to a record in the supplier table.

Supplier data

id	Name
1	horsepower
2	huawei

Contact data

id	Supplier ID	connect
1	1	John
2	1	Smith
3	1	meeting
4	2	U.S. Department of Energy
5	2	wick

Now, I want to make a query that should return the following results

id	Name	connect
1	horsepower	John, Smith, Will
2	huawei	U.S. Department of Energy, Wake

Or the following results should be returned

id	Name	connect	connect	connect
1	horsepower	John	Smith	meeting
2	huawei	U.S. Department of Energy	wick

Answer 1

You can use the MySQL GROUP_CONCAT aggregate function to get your first output table. Its own ORDER BY clause will allow you to check the concatenation order of rows.

SELECT s.ID,
       s.Name,
       GROUP_CONCAT(c.Contact ORDER BY c.id)
FROM       Supplier s
INNER JOIN Contact c
        ON s.ID = c.supplierId
GROUP BY s.ID,
         s.Name

You can use the window function ROW_NUMBER to assign a ranking to each row in the Contact table by partitioning by supplier. The contacts are then divided into three columns using an IF statement, which checks the three possible values ​​for the ranking. MAX Aggregation functions will allow you to remove null values.

SELECT s.ID,
       s.Name,
       MAX(IF(c.rn = 1, c.Contact, NULL)) AS Contact1,
       MAX(IF(c.rn = 2, c.Contact, NULL)) AS Contact2,
       MAX(IF(c.rn = 3, c.Contact, NULL)) AS Contact3
FROM       Supplier s
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY supplierId
                                        ORDER     BY id) AS rn
            FROM Contact ) c
        ON s.ID = c.supplierId
GROUP BY s.ID,
         s.Name;

The second query may not work if there are more than three customers per supplier. In this case, you can modify the query to include the maximum number of suppliers possible, or use a prepared statement. If you really need such a solution, please leave a comment below.

For better understanding you can use these solutions here. The first solution works with any MySQL version, while the second solution works with MySQL 8.