SQL, How to query with multiple foreign keys in a table?

Question

I have a projects table which has two foreign keys for users (user_id and winner_user_id), one for the owner of the project and one for the winner of the project. Something like

+----------------+-------------------------+------+-----+---------+----------------+
| Field          | Type                    | Null | Key | Default | Extra          |
+----------------+-------------------------+------+-----+---------+----------------+
| project_id     | int(10) unsigned        | NO   | PRI | NULL    | auto_increment | 
| start_time     | datetime                | NO   |     | NULL    |                | 
| end_time       | datetime                | NO   |     | NULL    |                | 
| title          | varchar(60)             | NO   |     | NULL    |                | 
| description    | varchar(1000)           | NO   |     | NULL    |                | 
| user_id        | int(11)                 | NO   |     | NULL    |                | 
| winner_user_id | int(10) unsigned        | YES  |     | NULL    |                | 
| type           | enum('fixed','auction') | YES  |     | NULL    |                | 
| budget         | decimal(10,0)           | YES  |     | NULL    |                | 
+----------------+-------------------------+------+-----+---------+----------------+

Now I am trying in a single query to get information about projects and the data about both of the users.

So I formulated a query like

SELECT projects.project_id, projects.title, projects.start_time,
            projects.description, projects.user_id, projects.winner_user_id,
            users.username as owner, users.username as winner
        FROM projects,users   
        WHERE projects.user_id=users.user_id
        AND projects.winner_user_id=users.user_id

Which returns an empty set obviously. The real problem is how do I reference these different user_ids. I even tried using the AS keyword and then using the name I had created in the same sql query but apparently that doesn't work.

To make things clear in the end I would like something like

+------------+-------------------------------------------------+---------------------+---------+----------------+--------------+--------------+
| project_id | title                                           | start_time          | user_id | winner_user_id | owner        | winner       |
+------------+-------------------------------------------------+---------------------+---------+----------------+--------------+--------------+
|          1 | CSS HTML Tableless expert for site redesign     | 2009-09-01 21:07:26 |       1 |              3 | mr X        | mr Y        | 
|          2 | High Quality Ecommerce 3-Page Design HTML & CSS | 2009-09-01 21:10:04 |       1 |              0 | mr X        | mr Z        | 

How can I construct a query to handle this?

Thanks in advance.

Answer 1

You are close, but you need to join the user table in twice, once on the owner and once on the winner. Use a table alias to differentiate the two.

SELECT 
      projects.project_id
    , projects.title
    , projects.start_time
    , projects.description
    , projects.user_id
    , projects.winner_user_id
    , users.username as owner
    , winnerUser.username as winner
FROM projects
INNER 
    JOIN users
    ON projects.user_id=users.user_id
INNER 
    JOIN users winnerUser
    ON projects.winner_user_id=winnerUser.user_id

Answer 2

SELECT ... FROM users AS winers, users AS owners 
WHERE projects.user_id=owners.user_id
        AND projects.winner_user_id=winners.user_id