Mysql - join multiple rows

This week I need fill one table from data from another tables. It was necessary join multiple columns to one column and join multiple rows to one row too.

I use function CONCAT and GROUP_CONCAT in Mysql

I create table RESULT_TABLE for result data. The table contains any data. The table look like:

mysql> select * from RESULT_TABLE;
+---------+------------+-------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+-------------+------------+
|                  meanwhile EMPTY                |
+---------+------------+-------------+------------+

In DB I have following table:

mysql> select * from BOOK;
+---------+------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_SCOPE |
+---------+------------+------------+
|       1 | title 1    | law        |
|       2 | title 2    | law        |
|       3 | title 3    | math       |
+---------+------------+------------+

mysql> select * from AUTHOR;
+-----------+------------------+-----------------+
| AUTHOR_ID | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME |
+-----------+------------------+-----------------+
|         1 | first1           | last1           |
|         2 | first2           | last2           |
|         3 | first3           | last3           |
|         4 | first4           | last4           |
|         5 | first5           | last5           |
+-----------+------------------+-----------------+

mysql> select * from BOOK_X_AUTHOR;
+---------+-----------+
| BOOK_ID | AUTHOR_ID |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         4 |
|       3 |         5 |
+---------+-----------+

---

In first step I join tables

mysql> select BOOK.BOOK_ID, BOOK.BOOK_TITLE, AUTHOR.AUTHOR_FIRSTNAME, AUTHOR.AUTHOR_LASTNAME, BOOK.BOOK_SCOPE from BOOK, AUTHOR, BOOK_X_AUTHOR
    -> where
    -> BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID and
    -> BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+------------------+-----------------+------------+
| BOOK_ID | BOOK_TITLE | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME | BOOK_SCOPE |
+---------+------------+------------------+-----------------+------------+
|       1 | title 1    | first1           | last1           | law        | 
|       1 | title 1    | first2           | last2           | law        | 
|       2 | title 2    | first3           | last3           | law        | 
|       2 | title 2    | first4           | last4           | law        | 
|       3 | title 3    | first5           | last5           | math       | 
+---------+------------+------------------+-----------------+------------+

---

In second step I use function concat and alias. Function concat return more columns as one column. With alias we can return another column name (BOOK_AUTHOR).

mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+--------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR  | BOOK_SCOPE |
+---------+------------+--------------+------------+
|       1 | title 1    | first1 last1 | law        | 
|       1 | title 1    | first2 last2 | law        | 
|       2 | title 2    | first3 last3 | law        | 
|       2 | title 2    | first4 last4 | law        | 
|       3 | title 3    | first5 last5 | math       | 
+---------+------------+--------------+------------+

---

In third step I use function GROUP_CONCAT. You notice the clause GROUP BY at the end of select. I join multiple rows from table AUTHOR to one colum.

mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat(concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> group by BOOK_ID;
+---------+------------+---------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR               | BOOK_SCOPE |
+---------+------------+---------------------------+------------+
|       1 | title 1    | first2 last2,first1 last1 | law        | 
|       2 | title 2    | first4 last4,first3 last3 | law        | 
|       3 | title 3    | first5 last5              | math       | 
+---------+------------+---------------------------+------------+

---

I add SEPARATOR and clause ORDER BY to function GROUP_CONCAT

mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
    -> ORDER BY AUTHOR.AUTHOR_ID
    -> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> GROUP BY BOOK_ID;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR                | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
|       1 | title 1    | first1 last1, first2 last2 | law        | 
|       2 | title 2    | first3 last3, first4 last4 | law        | 
|       3 | title 3    | first5 last5               | math       | 
+---------+------------+----------------------------+------------+

---

At the end I inserting result to RESULT_TABLE

mysql> insert into RESULT_TABLE (BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, BOOK_SCOPE)
    -> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
    -> ORDER BY AUTHOR.AUTHOR_ID
    -> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> GROUP BY BOOK_ID;

mysql> select *  from RESULT_TABLE;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR                | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
|       1 | title 1    | first1 last1, first2 last2 | law        | 
|       2 | title 2    | first3 last3, first4 last4 | law        | 
|       3 | title 3    | first5 last5               | math       | 
+---------+------------+----------------------------+------------+