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

database, mysql, sql

Solution

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

Problem

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.

Original source