asked on

How to select data from multiple mysql tables using sql statement?

Hi, I wrote the following script to select the following information from the database below:
- Rowkey
- Title + Subtitle as Full Title
- Coutributor Name as Author - If Not Exist " "
- Coutributor2 Name as Author2 - If Not Exist " "
- Coutributor3 Name as Author3 - If Not Exist " "
- Coutributor4 Name as Author4 - If Not Exist " "
- Coutributor5 Name as Author5 - If Not Exist " "
- Coutributor6 Name as Author6 - If Not Exist " "
- Coutributor7 Name as Illustrator - If Not Exist " "
- Coutributor8 Name as Translator - If Not Exist " "
- Coutributor9 Name as Editor - If Not Exist " "
- Publisher
- Format
- ISBN
- ASIN

Assuming the the first author field is always filled I can select the first author, however that may not always be the case. Also if it exists, I would like to select and concatenate Authors 1 - 6, as well as select the following fields; Illustrator, Translator and Editor.

Could anyone please demonstrate how I can do this?

SELECT READERWARE.ROWKEY, RTRIM(CONCAT(READERWARE.TITLE, IFNULL((READERWARE.SUBTITLE), ' '))) AS 'FULL TITLE', CONTRIBUTOR.NAME AS AUTHOR,
FORMAT_LIST.LISTITEM AS ITEM_FORMAT, PUBLISHER_LIST.LISTITEM AS PUBLISHER, READERWARE.ISBN, READERWARE.AM_ASIN 
FROM (((READERWARE 
INNER JOIN CONTRIBUTOR ON READERWARE.AUTHOR=CONTRIBUTOR.ROWKEY)
INNER JOIN FORMAT_LIST ON READERWARE.FORMAT=FORMAT_LIST.ROWKEY)
INNER JOIN PUBLISHER_LIST ON READERWARE.PUBLISHER=PUBLISHER_LIST.ROWKEY);

Open in new window

rwaredb---Copy.sql

Ryan Chong

I would like to select and concatenate Authors 1 - 6

try use concat_ws function.

for example:

SELECT READERWARE.ROWKEY, RTRIM(CONCAT(READERWARE.TITLE, IFNULL((READERWARE.SUBTITLE), ' '))) AS 'FULL TITLE', 
CONCAT_WS (', ', C1.NAME, C2.NAME, C3.NAME, C4.NAME, C5.NAME, C6.NAME) AUTHOR,
FORMAT_LIST.LISTITEM AS ITEM_FORMAT, PUBLISHER_LIST.LISTITEM AS PUBLISHER, READERWARE.ISBN, READERWARE.AM_ASIN 
FROM READERWARE 
left JOIN CONTRIBUTOR C1 ON READERWARE.AUTHOR=C1.ROWKEY
left JOIN CONTRIBUTOR C2 ON READERWARE.AUTHOR2=C2.ROWKEY
left JOIN CONTRIBUTOR C3 ON READERWARE.AUTHOR3=C3.ROWKEY
left JOIN CONTRIBUTOR C4 ON READERWARE.AUTHOR4=C4.ROWKEY
left JOIN CONTRIBUTOR C5 ON READERWARE.AUTHOR5=C5.ROWKEY
left JOIN CONTRIBUTOR C6 ON READERWARE.AUTHOR6=C6.ROWKEY
INNER JOIN FORMAT_LIST ON READERWARE.FORMAT=FORMAT_LIST.ROWKEY
INNER JOIN PUBLISHER_LIST ON READERWARE.PUBLISHER=PUBLISHER_LIST.ROWKEY;

Open in new window

MySQL: CONCAT_WS Function
https://www.techonthenet.com/mysql/functions/concat_ws.php