SELECT t1.name AS Nomi, t2.name AS Propo FROM award t0 INNER JOIN user t1 ON t0.nominee=t1.uid INNER JOIN user t2 ON t0.proposer=t2.uid
> -----Original Message----- > From: Michael Carter [mailto:[EMAIL PROTECTED]] > Sent: Saturday, November 30, 2002 11:51 AM > To: [EMAIL PROTECTED] > Subject: Select: JOIN magic? > > > Hello. I'm trying to select and display from a couple tables > I have. I have an awards table, which has info on an award a > user has earned. I've also a user table which lists all the > users. What I'm trying to do is display information about an > award, but can't find the right SELECT syntax to make it work > the way I want. The award table has two associations to the > user table: the nominee and the proposer: > > What I want: > > Award | Nominee | Proposer | Awarded on | > 1 | Joe Shmoe | Fred Smith | 2002-11-29 | > > What I get: > > Award | Nominee | Proposer | Awarded on | > 1 | Joe Shmoe | 3 | 2002-11-29 | > > The (simplified) tables look like this: > > +---------------+ +-----------------+ > | award | | user | > +---------------+ +-----------------+ > | uid (int) | | uid (int PRI) | > | nominee (int) | | name (varchar) | > | proposer (int)| +-----------------+ > | awarded (Date)| > +---------------+ > > I've been doing the following to get the basic info: > > SELECT * FROM award LEFT JOIN user ON award.nominee=user.uid; > > This gets me the info for the nominee, including name and > everything else. I'd like to spare making a whole other query > to the DB to get the name for the Proposer, but I can't think > of a way to make it work! > > Any help would be appreciated. :) > > Regardez, > > Michael Carter > Pilot/Programmer > > --------------------------------------------------------------------- > 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 > > > > --------------------------------------------------------------------- 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