Hi all, I've been training someone on how to use MySQL, and apparently I'm suffering from brain fade big time. The user has created two tables; an authors table and a publications table.
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. 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? I'm a php coder, but in this case, the user wishes to do this with straight sql queries. And for whatever reason, I can't come up with a solution to this. If anyone has an idea or two to throw my way, I would really appreciate it! /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer <*> 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] http://www.seweb.uci.edu/techsupport If it be now, 'tis not to come; if it be not to come, it will be now; if it be not now, yet it will come: the readiness is all." -- William Shakespeare, "Hamlet." --------------------------------------------------------------------- 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
