1

this is a revised question from previous one. I decided to open a new question since the scope has changed.

This is what I want to achieve.

List the last name of the author(s) of the book in an "Author" column, the last name of the author to be listed first, followed (after a comma) by that of the author to be listed second if any; and if there is a third author, put it after the second author's name. The order of the author of a book is listed in the au_ord column (1=First Author, 2=Second Author, 3=Third Author).

Any idea how to achieve this in MYSQL output?

enter image description here

Those are the source table. The desired output is something like this:

enter image description here

ocinisme
  • 301
  • 2
  • 10
  • This is the previous question's link http://stackoverflow.com/questions/13126011/matching-different-value-within-one-row/13126415#comment17849651_13126415 – ocinisme Oct 30 '12 at 04:20
  • Could you post sample values, and the desired output? That often helps more than textual description.. – nawfal Oct 30 '12 at 05:14
  • posted dude! I hope it is clearer now. – ocinisme Oct 30 '12 at 05:19
  • Is the first table sample values, and the second table desired output? (in that case tell us where the field `title` is from). I guess not, so post the desired output as well – nawfal Oct 30 '12 at 05:21
  • updated bro! Anything else that is not clear please give me a heads up. I am stuck here. Trying to play around with the CONCAT function but cannot figure out anything. – ocinisme Oct 30 '12 at 05:37
  • What is the role of the second table you posted here? And where is the table from which you want the author's name? And what is the criterion to have only title_id `TC7777`? – nawfal Oct 30 '12 at 05:54
  • I need to take the **title** from the second table. There is no particular criteria, I just need to list all the author(s) of a book. – ocinisme Oct 30 '12 at 06:42

2 Answers2

3

This should give your first sample output:

SELECT title_id, au_ord, au_lname
FROM title_authors
LEFT JOIN authors
ON authors.au_id = title_authors.au_id
WHERE title_id = 'TC7777'
ORDER BY au_ord;

and this should give the second:

SELECT title_id, GROUP_CONCAT(au_lname ORDER BY au_ord SEPARATOR ', ')
FROM title_authors
LEFT JOIN authors
ON authors.au_id = title_authors.au_id
GROUP BY title_id
HAVING title_id = 'TC7777';
Terje D.
  • 6,250
  • 1
  • 22
  • 30
  • I dont think `SEPARATOR` keyword is required here. `GROUP_CONCAT` automatically does the separation for you with commas. – nawfal Oct 30 '12 at 08:41
  • @nawfal: You are right that comma is the default separator for `GROUP_CONCAT`. In this answer, however, the separator is a comma followed by a space. – Andriy M Oct 30 '12 at 10:24
2

All you need is a GROUP_CONCAT function.

SELECT title_id, GROUP_CONCAT(au_lname ORDER BY au_ord)
FROM table
GROUP BY title_id

The GROUP BY clause groups all individual (distinct) titles, and the GROUP_CONCAT on it concatenates all the authors of it. This would list all the titles and their corresponding authors. If au_lname is from another table than from title_id table, then you will have to use appropriate joins. I am not sure what are the table names from your question.

nawfal
  • 70,104
  • 56
  • 326
  • 368