* Vince LaMonica [...] > The authors table has 4 cols: id [primary/smallint/autoincrement], > last_name, middle_name, first_name. > > The publications table has several cols, but the ones most important to > this question are: id [primary/smallint/autoincrement], author1 > [smallint, > foreign key to authors.id/default NULL], author2 [same], author3 [same], > author4 [same], author5 [same], and year [char(4)]. > > The user has created multiple author cols in the publications table > because the order of the author matters [eg: it is better for someone to > be an author1 than an author2 or a dreaded author5]. Some publications > have 1 author, some have up to five.
hm... This is not good db design... I would use a third table: 'paper_authors' with the columns 'paper_id', 'author_id' and 'prio', where 'prio' is a tinyint with the values 1-5. > Putting together a simple query to find out the names of the > author[s] for each publication: > > SELECT author1, author2, author3, author4, author5, year > FROM `papers` GROUP BY papers.id ORDER BY `year` ASC > > This produces a nice 'table' of each publication's 1-5 authors, listed by > their id. > > How do I alter the query to replace their id with > authors.last_name? You need to JOIN with the authors table five times: 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 > HTH, -- Roger --------------------------------------------------------------------- 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
