orting a table by the last_name from another table

[danielkessler]

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

[PAVB]

so... what was your question?

[danielkessler]

How do I sort the list of publications by the last name of the first author?

If it's an improper questions, just say so.

[danielkessler]

and I suppose I'd also like to know why I can't use a comma, or how to use a comma, when I'm concatenating two fields.

[PAVB]

If it's an improper questions, just say so

Relax, all questions are proper...

How do I sort the list of publications by the last name of the first author?

What tells you in your data structure which one is the first author?

and I suppose I'd also like to know why I can't use a comma, or how to use a comma, when I'm concatenating two fields

There is nothing wrong with it, try something like...

select a_fname || ',' a_lname
from publications_authors
where rownum < 2;

... just to test it.

[danielkessler]

Relax, all questions are proper...

I'm sorry, but it's morning and I didn't state that well.

What I meant is that I have little experience working with datasets from related tables and often, I don't know what can and cannot be done. So, I don't know if I'm asking something that's clearly not doable or shouldn't be done by the DB.

What tells you in your data structure which one is the first author?

It's the order that they were entered, so it's the UID publicationa_id

There is nothing wrong with it, try something like...

select a_fname || ',' a_lname
from publications_authors
where rownum < 2;

... just to test it.

That works, but doesn't bring in a_lname because there's not a pipe after the comma - but the comma does the right thing.
I had a double pipe: a_fname || ',' || a_lname and that works with a colon in between, but not with a comma. I receive the error:
[Table (rows 8 columns A_FNAME||','||A_LNAME): [A_FNAME||','||A_LNAME: coldfusion.sql.QueryColumn@429784] ] is not indexable by A_FNAME||'

[PAVB]

That works, but doesn't bring in a_lname because there's not a pipe after the comma - but the comma does the right thing

I'm sorry, I forgot to type the pipe after ','

Not reason for it not to work... a ColdFusion issue perhaps?... can you trick ColdFusion to show you the query is trying to submit?

It's the order that they were entered, so it's the UID publicationa_id

So... if a publication has three authors you will have three entries in publications_authors, all of them with the same publicationa_id value, am I right?

If YES... you do not have an order there, Oracle might retrive first any of the three entries matching the key.

[danielkessler]

I'm sorry, I forgot to type the pipe after ','

Not reason for it not to work... a ColdFusion issue perhaps?... can you trick ColdFusion to show you the query is trying to submit?

The query is pretty straight-up since I'm just doing a test query for the comma, as you suggested. I added debug="yes" result="queryResult" and it does a dump of the sql. It's:
SELECT a_fname || ',' || a_lname FROM publications_authors
The dump of the query is here (it's the one in blue):
http://hhp.umd.edu/research/publications_display.cfm

So... if a publication has three authors you will have three entries in publications_authors, all of them with the same publicationa_id value, am I right?

If YES... you do not have an order there, Oracle might retrive first any of the three entries matching the key.

No, they have incrementing publicationa_id values, using nextVal. They all have the same publication_id from the publications table. So I want to sort all those publications in the publications table by the first author's last name from the publications_authors table.

Honestly, I'm not even sure how to work with the dataset once I get it since it'll show 3 entries for 3 authors for each publication (for example).

I know related tables are surely the way to go for flexibility, but I'm often left scatching my head when using them.

[PAVB]

Lets try to solve the issues one at a time.

Query below should serve as a template for what you want, could you please try it?

select p.publication_id,
p.title,
a.a_lname
from publications p,
publications_author a
where p.publication_id = a.publication_id
a.publicationa_id =
(select min(aa.publicationa_id)
from publications_author aa
where p.publication_id = aa.publication_id
)
order by p.title, a.a_lname
;


In regards to the comma issue... either I'm missing something really big or it's related ColdFusion, don't know why. In your case I would try some ColdFusion forum and check for the comma issue.

[danielkessler]

alright. That query shows promise, but in my testing I found that I'm doing some things incorrect elsewhere so I've not been able to check it yet. I'm testing now.

As for the "concatenate with a comma" issue, I posted to cf-talk (www.houseoffusion) which is a really good list, and they said that I need to alias the output for it to work. So:
a_fname || ',' || a_lname
became
a_fname || ',' || a_lname as the_name

and it worked.