SQL SELECT statements with lists when order matters

[PorkCharSui]

I'm trying to build a database of research publications where we might have multiple authors per papers and the order these authors are displayed is critical. Right now I've implemented a publication table that stores in pub_authors a list of author_id(s) (ie... 42,3,44,2). Based on the order of the list in publications I'd like to return from a query to the authors table a result set who's first record is id 42, second is 3... and so on.

Right now I'm using this select statement, but it returns a result set ordered ASC by author_id:
SELECT author_id, author_first, author_middle, author_last, author_institution
FROM authors
WHERE author_id
IN ( $publication[pub_authors] )

How can I accomplish this or is there a better way of doing this?

Thanks!

 

[mikeblas]

ORDER BY is the only way to guarantee ordering in SQL.

 

[Scoobydo]

Provided I understand the question correctly, looks like you need to join the tables.

However, the way you join will be dependant on how the publication table is structured.

For example,

If the publication table is like (which is actually the best way to do it, you would also need to have another table to hold publication data, this is just a mapping table between the two):

idPublication, idAuthor
----------------------------------
1 43
1 3
1 10
2 20
2 10
2 30

You can join the two tables like:

SELECT author.idAuthor FROM Publication
INNER JOIN Author on publications.idauthor = author.idauthor

Doing that will cause all information from the publication table and author table to be available in the select list (but in my example it only pulls author.idauthor) and is linked by idauthor and is ordered as they are in publications (as that is the FROM table.)

If you are using a comma seperated list, thats another story, but I personally don't like that solution as its limited and harder to index.

 

[PorkCharSui]

Scoobydo:

Thanks for the advice, I'm breaking my author order information into another table (avoiding the list implementation) so I can associate the following per row:

pub_id, author_id, and order

for pub_id 1 my order table would look like this (based on the info I gave in my first post)

idPub, idAuthor, order
================
1   42    1
1    3    2
1    44    3
1    2    4

Now when I'm doing my query I simply join this table and my author table, sorting by the order column. Yeah?

 

[ameoba]

try it.

 

[maxedoutcc]

yes in theory that would work just fine.

I would recommend changing your column name from "order" to something else like orderID. In most databases order is a semi-reserved word.

 

[PorkCharSui]

Thanks for all the guidence. I got it all working now!!!

I started using Navicat instead of phpMyAdmin and my workflow speed just about doubled. If you do a lot of mysql dev check this out:

http://www.navicat.com