Sunday, October 25, 2009

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

2 comments: