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.

[danielkessler]

Yay! that worked or just about. I had to switch the ORDER BY from p.title, a.a_lname to a.a_lname,p.title

Now, I have two more issues to build in.
First, this only returns the last name and that's great for ordering, but I'll have to display all the authors information in the end like "lastname,firstInitial,2ndLastName,2ndFirstInitial", so it's going to need to include all the information. Since we're getting just the one entry (with the min), then maybe I can query and append the information into a variable before the min is done?

Secondly, in the authors table, the author can either be a_fname, a_lname or an ID (number) which references our People table for those in our people table. I had code to do this, but I've not been able to work it into this code so far. Since we're sorting on last name, this would have to be done before the sort. Here's the code where I was joining this information together:

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

Thank you for any assistance and thanks VERY MUCH for the assistance so far. :D

[PAVB]

mmhhhh... I see.

Here is a hint, do it with cursors.

Two cursors.
Load basic data in outer cursor like last_name, book and whatever you need, already sorted as you want the output to be.
Load authors in inner cursor for each outer cursor entry

[danielkessler]

I was just looking at cursors last night and trying to figure out how to write them. Nice to know I was on the right path.

thank you for your help and pointers. I learned a good bit.

[PAVB]

Glad to help :)