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
HonyaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

as well as select the following fields; Illustrator, Translator and Editor.
you need to tell us which tables to join with, in general a LEFT JOIN clause should be used.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, get what you mean for Illustrator, Translator and Editor. so try this instead:

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,
Illustrator.Name Illustrator,
Translator.Name Translator,
Editor.Name Editor,
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

left JOIN CONTRIBUTOR Illustrator ON READERWARE.ILLUSTRATOR=Illustrator.ROWKEY
left JOIN CONTRIBUTOR Translator ON READERWARE.TRANSLATOR=Translator.ROWKEY
left JOIN CONTRIBUTOR Editor ON READERWARE.EDITOR=Editor.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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HonyaAuthor Commented:
Hey Ryan, That's It! I also used the Left join on the Format and Publisher fields. It works beautifully. Thank you!
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Coool, glad that it works!

you may proceed to close this question then.
HonyaAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.