Hi, I wrote the following script to select the following information from the database below:
- 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 " "
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
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);