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=1
>
>
>
> --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
