This one seems to be a simple problem, but I can't make it work in a single
select or nested select. Retrieve the authors and (if any) advisers of a
paper (article) into one row.
I order to explain the problem, here are the two data tables (pseudo)
papers (id, title, c_year)
persons (id, firstname, lastname)
plus a link table w/one extra attribute (pseudo):
paper_person_roles(
paper_id
person_id
act_role ENUM ('AUTHOR', 'ADVISER')
)
This is basically a list of written papers (table: papers) and a list
of staff and/or students (table: persons)
An article my have (1,N) authors.
An article may have (0,N) advisers.
A person can be in 'AUTHOR' or 'ADVISER' role (but not at the same time).
The application eventually puts out table rows containing the following
entries:
TH: || Paper_ID | Author(s) | Title | Adviser(s) |
TD: || 21334 |John Doe, Jeff Tucker|Why the moon looks yellow|Brown, Rayleigh|
...
My first approach was like:
select/extract a full list of articles into the application, eg.SELECT
q.id, q.title
FROM
papers AS q
ORDER BY
q.c_year
and save the results of the query into an array (in the application). After this
step, loop over the array of the returned information and retrieve authors and
advisers (if any), via prepared statement (? is the paper's id) from the link table
like:APPLICATION_LOOP(paper_ids in array)
SELECT
p.lastname, p.firstname, r.act_role
FROM
persons AS p, paper_person_roles AS r
WHERE
p.id=r.person_id AND r.paper_id = ?
# The application does further processing from here (pseudo):
foreach record from resulting records
if record.act_role eq 'AUTHOR' then join to author_column
if record.act_role eq 'ADVISER' then join to avdiser_column
end
print id, author_column, title, adviser_column
APPLICATION_LOOP
This works so far and gives the desired output. Would it make
sense to put the computation back into the DB?
I'm not very proficient in nontrivial SQL and can't find a
solution with a single (combined or nested) select call. I
tried sth. like SELECT
q.title
(CONCAT_WS(' ',
(SELECT p.firstname, p.lastname AS aunames
FROM persons AS p, paper_person_roles AS r
WHERE q.id=r.paper_id AND r.act_role='AUTHOR')
)
) AS aulist
FROM
papers AS q, persons AS p, paper_person_roles AS r
in several variations, but no luck ...
Maybe there is some chance?
Thanks in advance
r.b.
