-
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
-
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
-
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.
-
