Hi all, I've got a SQL query I can't seem to generate, and am hoping someone out there has some ideas. I can not use subselects [using mysql 3.23, which doesn't support subselects].
I have 3 tables, laid out as follows: mysql> desc authors; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | smallint(3) | | PRI | NULL | auto_increment | | last_name | varchar(60) | | | | | | first_name | varchar(60) | YES | | NULL | | | middle_name | varchar(60) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ mysql> desc papers; +---------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+----------------+ | id | smallint(3) unsigned | | PRI | NULL | auto_increment | | year | varchar(4) | | | | | | title | varchar(255) | YES | | NULL | | [snip] mysql> desc paper_authors; +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | paper_id | smallint(3) unsigned | | | 0 | | | author_id | smallint(3) unsigned | | | 0 | | | priority | tinyint(1) unsigned | | | 0 | | +-----------+----------------------+------+-----+---------+-------+ The last table, paper_authors, is mainly to be used to join the first two tables together. The priority column contains a number from 1 to 5. So basicly, a paper can have 1-5 authors, and I need to keep track of which author is #1, #2, #3, etc. I need to create a SQL statement that will output something like this: --+-----------+----------+----------+---------+---------+------+ id| author1 | author2 | author3 | author4 | author5 | year | --+-----------+----------+----------+---------+---------+------+ 4| last_name | ... 1999 Note that the columns labeled author2-author5 may be NULLs. Not all papers have 5 authors. I've created a sql statement attempts to produce the above results: SELECT papers.id, a1.last_name as author1, a2.last_name as author2, a3.last_name as author3, a4.last_name as author4, a5.last_name as author5, papers.year FROM paper_authors, papers, authors AS a1 LEFT JOIN authors AS a2 ON (a2.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '2') LEFT JOIN authors AS a3 ON (a3.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '3') LEFT JOIN authors AS a4 ON (a4.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '4') LEFT JOIN authors AS a5 ON (a5.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '5') WHERE a1.id = paper_authors.author_id AND paper_authors.priority = '1' AND paper_authors.paper_id = papers.id ORDER BY year ASC The above query performs a join on author where priority = '1' [because every paper will have at least 1 author], and then 4 left joins on the priorities 2-5. However, the result of this query produces only accurate results for the join [eg: all the author '1's are right, but author2-5 are all NULL]. What am I doing incorrectly? I know it's something simple I'm not seeing. Any help you can provide would be most appreciated. Thanks! /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 Microsoft asks you where you want to go. UNIX gets you there. --------------------------------------------------------------------- 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
