CODEWITHSUNDEEP
mysqlsql
ocinisme
ocinisme

Reputation: 301

Matching Different Value within One Row II

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

Upvotes: 1

Views: 64

Answers (2)

nawfal
nawfal

Reputation: 73163

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.

Upvotes: 2

Terje D.
Terje D.

Reputation: 6315

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';

Upvotes: 3

Related Questions