> SELECT papers.id, > concat(a1.first_name,' ',a1.middle_name,' ',a1.last_name) > as author1, > concat(a2.first_name,' ',a2.middle_name,' ',a2.last_name) > as author2, > concat(a3.first_name,' ',a3.middle_name,' ',a3.last_name) > as author3, > concat(a4.first_name,' ',a4.middle_name,' ',a4.last_name) > as author4, > concat(a5.first_name,' ',a5.middle_name,' ',a5.last_name) > as author5, > year > FROM papers > LEFT JOIN authors a1 ON a1.id = papers.author1 > LEFT JOIN authors a2 ON a2.id = papers.author2 > LEFT JOIN authors a3 ON a3.id = papers.author3 > LEFT JOIN authors a4 ON a4.id = papers.author4 > LEFT JOIN authors a5 ON a5.id = papers.author5 > ORDER BY year ASC > > (Don't know why you would use GROUP BY in this case.) > > LEFT JOIN is used because not all papers have five authors. > Read more about > the different types of JOIN in the manual: <URL: > http://www.mysql.com/doc/en/JOIN.html >
Purely as an informational point, this should work as well: ... FROM papers, authors AS a1 LEFT JOIN authors AS a2 ON a2.id = papers.author2 LEFT JOIN authors AS a3 ON a3.id = papers.author3 LEFT JOIN authors AS a4 ON a4.id = papers.author4 LEFT JOIN authors AS a5 ON a5.id = papers.author5 WHERE a1.id = papers.author1 ORDER BY year ASC Note that the only difference is that the first LEFT JOIN has been replaced with a plain JOIN. I have no idea if this would be more efficient in practice but it should make the nature of the data a tiny bit clearer -- a publication presumably must have at least one author. :) -JF --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
