Re: simple query turned ugly
* Vince LaMonica [...] > 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 This is wrong, because "paper_authors.priority = '1'" in the where clause will prevent you from finding any paper_authors with priority = '2'. You must put this criteria in the ON clause of the LEFT JOIN: SELECT papers.id, a1.last_name as auth1, a2.last_name as auth2, papers.year FROM papers LEFT JOIN paper_authors pa1 ON pa1.paper_id = papers.id AND pa1.priority = 1 LEFT JOIN authors a1 ON pa1.author_id = a1.id LEFT JOIN paper_authors pa2 ON pa2.paper_id = papers.id AND pa2.priority = 2 LEFT JOIN authors a2 ON pa2.author_id = a2.id ORDER BY year ASC Another tip: If you rename autors.id to autors.author_id and papers.id to papers.paper_id, you can use NATURAL JOIN, which makes things a bit simpler: SELECT papers.id, last_name, priority, papers.year FROM papers NATURAL JOIN paper_authors NATURAL JOIN authors WHERE priority IN (1,2) ORDER BY year ASC (This is a different query: it returns all paper/author pairs matching the WHERE clause (i.e., priority 1 or 2), one author for each row in the result set, while the first query returns two authors in one row for each paper.) The 'NATURAL JOIN' is described like this in the manual: * The `NATURAL [LEFT] JOIN' of two tables is defined to be semantically equivalent to a `INNER JOIN' or a `LEFT JOIN' with a `USING' clause that names all columns that exist in both tables. HTH, -- Roger sql - 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
Re: simple query turned ugly
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
RE: simple query turned ugly
> 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: 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
Re: simple query turned ugly
* 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: 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
simple query turned ugly
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
