>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.
It's there so that I know who entered the publication. That way, I can restrict editing to the person that entered it. >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 That worked pretty well. I unfortunately left out/didn't explain it thoroughly (so sorry). Each publication will have multiple authors and it should only sort 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. Otherwise, you're query looked good. So to recap, the ordering of the publications is by first author. The ordering of the authors in the display of each publication is by the way they were entered. example: benas, j.,achary,q The Mountains of Schoenberg zaine,p,benas,j The forest of my mind Did I make sense? Here's a dump where I join the fname,lname of people in the authors table and display the publications: http://hhp.umd.edu/research/publications_display.cfm Here's the current example of this data using the query that you posted: http://hhp.umd.edu/research/publications.cfm Thank you for listening. daniel ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276122 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
