On Thu, 14 Nov 2002, Roger Baklund wrote: } * Vince LaMonica [snip] } > 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.
You are correct, thanks. I added a paper_authors table with the above cols [except I called 'prio' 'priority' instead. Applying your left join [as well as Jon Frisby's suggestion] works great for the non-normalized version of this small database. But now that I have the 'join table' [paper_authors] above, I'm not so certain how to produce the same results. I'm currently attempting this by doing [only trying priority 1 and 2 right now]: SELECT papers.id, a1.last_name as auth1, a2.last_name as auth2, 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') WHERE a1.id = paper_authors.author_id AND paper_authors.priority = '1' AND paper_authors.paper_id = papers.id ORDER BY year ASC Here's the 3 tables: 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] [i took the author1 - author5 cols out] 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 | | +-----------+----------------------+------+-----+---------+-------+ Running the above query produces a the correct priority 1 author, but the priority 2 author rows are all NULL. Running the query with all 4 left joins results in the same NULL values in the auth2, auth3, etc, cols. I've got to be missing something basic here, right? Any further assistance would be most appreciated. /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 Tower: "Delta Zulu Romeo, turn right now and report your heading." Pilot: "Wilco. 341, 342, 343, 344, 345..." --------------------------------------------------------------------- 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
