Re: simple query turned ugly

2002-11-18 Thread Roger Baklund
* 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.au

Re: simple query turned ugly

2002-11-14 Thread Vince LaMonica
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 } > ha

RE: simple query turned ugly

2002-11-14 Thread Jon Frisby
> 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,' ',

Re: simple query turned ugly

2002-11-14 Thread Roger Baklund
* 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

simple query turned ugly

2002-11-14 Thread Vince LaMonica
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. Th