I have a Publications table (see tables below for all tables). It has a publications_authors table that has a many-to-one relationship to publications because a publication may have many authors. In addition, in Authors, either id can be filled with a number that relates to the People table, or with fname, lname if the person isn't
in the People table.

Each publication will have multiple authors and it should only sort the list of publications by the lastname,firstname of the first author (see example). The authors for each publication must display in the order that they were entered though, so I thought I could concatenate them, then maybe sort.

example:
benas, j.,achary,q The Mountains of Schoenberg
zaine,p,benas,j The forest of my mind

Here's my current query:
SELECT publication_name,publicationa_id,lastname || ':' || firstname
FROM (

SELECT p.publication_name,
CASE WHEN o.lname is not null then o.lname ELSE a.a_lname END as lastname,
CASE WHEN o.fname is not null THEN o.fname ELSE a.a_fname END as firstname, a.publicationa_id

FROM publications p INNER JOIN publications_authors a ON p.publication_id = a.publication_id
LEFT JOIN people o ON a.id = o.id
ORDER BY publicationa_id asc
)


and the results:
http://hhp.umd.edu/research/publications_display.cfm

and I don't seem to be able to add the ',' when I concatenate the lastname and first name.


create table publications (
publication_id NUMBER Primary Key,
id NUMBER REFERENCES people(id),
date_added DATE,
date_modified DATE,
published_day NUMBER,
published_month NUMBER,
published_year NUMBER,
publication_name VARCHAR2(300), // designates the name of the book/
journal that the publication may be in
title VARCHAR2(300),
upload_path VARCHAR2(200)
)


create table publications_authors (
publicationa_id NUMBER Primary Key,
publication_id NUMBER REFERENCES publications(publication_id) ON
DELETE CASCADE,
id NUMBER REFERENCES people(id),
a_fname VARCHAR2(100),
a_mname VARCHAR2(20),
a_lname VARCHAR2(100)
)

create table PEOPLE (
id NUMBER Primary Key,
dateModified date,
type VARCHAR2(50),
fname VARCHAR2(100),
mname VARCHAR2(100),
lname VARCHAR2(100),
status VARCHAR2(20)
)

thank you for any assistance.

daniel