Try this.
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
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 2, 3
I'm not really sure what your foreign key in publications for the
people table is supposed to be doing. I'm assuming it references
something other than authors.
On 4/24/07, Daniel Kessler <[EMAIL PROTECTED]> wrote:
> 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.
>
> I've been asked to sort the publications by the last name of the
> author. Since these are separate tables, I don't see how to do
> that. It doesn't seem like it can be an ORDERED BY. I also don't
> see a realistic way to join these tables since authors is a many-to-
> one. Clearly I'm a bit out of my league here.
>
> I do have a method to make this work and I'm not sure if it's a good
> method. I can get my list of publications. Query the authors for
> that publication. If the author is an id, then I'd have to query the
> People table for the fname, lname of the author. Make the list of
> authors into a string, "#lname#,#fname#". Then use CF to make a
> column in the publications table query and add that string to the
> column. Then I can query the query with an ORDERED BY on that
> column. Sounds convoluted but I think it'd work.
>
> hmm, maybe I could query authors and join Publications and People
> since it would seem that any publication would have to have an author.
>
> Any thoughts? I suppose that I don't know how to work with the many-
> to-one relationships well.
>
>
> 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)
> )
>
>
>
> --
>
> Daniel Kessler
>
> College of Health and Human Performance
> University of Maryland
> Suite 2387 Valley Drive
> College Park, MD 20742-2611
> Phone: 301-405-2545
> http://hhp.umd.edu
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Macromedia ColdFusion MX7
Upgrade to MX7 & experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276105
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
