From: Peter Brawley
Date: June 7 2006 5:56pm
Subject: Re: Joins - Multiple rows from a single table
List-Archive: http://lists.mysql.com/mysql/198674
Message-Id: <44871362.6040202@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Geoffrey,

 >SELECT
 >  bugs.id, bugs.assignee, users.username, bugs.submitter
 >FROM fb_bugs AS bugs,
 >fb_users AS users
 >WHERE users.id = bugs.assignee

 >My problem is that I also want the users.username for bugs.submitter. 
How can I do that?

Join users a second time for bugs.submitter, eg ...

SELECT
  bugs.id, bugs.title, UNIX_TIMESTAMP(bugs.submitted), bugs.fix, 
bugs.assignee,
  users.username,
  users2.username AS Submitter,
  bugs.category,
  category.name,
  bugs.version,
  version.name,
  bugs.priority, bugs.haspatch
FROM fb_bugs AS bugs
INNER JOIN fb_users AS users ON bugs.assignee = users.id
INNER JOIN fb_users AS users2 ON bugs.submitter = users2.id
INNER JOIN fb_category AS category ON bugs.category = category.id
INNER JOIN fb_versions AS version ON bugs.version = version.id

PB

-----

Geoffrey Sneddon wrote:
> I've got the following query:
>
> SELECT `bugs`.`id`, `bugs`.`assignee`, `users`.`username`, 
> `bugs`.`submitter` FROM `fb_bugs` AS `bugs`, `fb_users` AS `users` 
> WHERE `users`.`id` = `bugs`.`assignee`
>
> My problem is that I also want the `users`.`username` for 
> `bugs`.`submitter`. How can I do that?
>
> Also, anyway to make the entire query better (the above is just the 
> relevant part):
>
> SELECT `bugs`.`id`, `bugs`.`title`, 
> UNIX_TIMESTAMP(`bugs`.`submitted`), `bugs`.`fix`, `bugs`.`assignee`, 
> `users`.`username`, `bugs`.`category`, `category`.`name`, 
> `bugs`.`version`, `version`.`name`, `bugs`.`priority`, 
> `bugs`.`haspatch`FROM `fb_bugs` AS `bugs`, `fb_users` AS `users`, 
> `fb_category` AS `category`, `fb_versions` AS `version` WHERE 
> `users`.`id` = `bugs`.`assignee` AND `category`.`id` = 
> `bugs`.`category` AND `version`.`id` = `bugs`.`version`
>
> Help will very much be appreciated, as SQL like this really isn't what 
> I'm good at :)
>
> - Geoffrey Sneddon
>
>
>
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=peter.brawley@stripped
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006