I have the following tables
Book table
id title authors 3 Linux
authors table
id firstname lastname fk 1 name name 3 2 name2 name 3
I would like to query authors table to get authors column filled something like this table using GROUP_CONCAT()
id title authors 3 Linux name name | name2 name2
For a single book whose ID you know, you can get the list of authors as a single value like this:
SELECT group_concat(firstname || ' ' || lastname)
FROM authors
WHERE fk = ?;
Just use this as a correlated subquery in the actual query:
SELECT id,
title,
(SELECT group_concat(firstname || ' ' || lastname, ' | ')
FROM authors
WHERE fk = book.id
) AS authors
FROM book;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments