Reputation: 9206
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.
Upvotes: 6
Views: 10559
Reputation: 6261
This should work, and return NULL if the winner in unknown (when winner_user_id is null)
SELECT projects.project_id,
projects.title,
projects.start_time,
projects.description,
projects.user_id,
projects.winner_user_id,
users_owner.username as owner,
users_winner.username as winner
FROM projects
INNER JOIN users AS users_owner ON users_owner.user_id = projects.user_id
LEFT OUTER JOIN users AS users_winner ON users_winner.user_id = projects.winner_user_id
Upvotes: 0
Reputation: 10630
SELECT u1.user_id AS user_id, u2.user_id AS AS winner_id
FROM projects p
INNER JOIN users u1 ON p.user_id=u1.user_id
INNER JOIN users u2 ON p.winner_user_id=u2.user_id
Upvotes: 0
Reputation: 401182
What about using something like this :
SELECT projects.project_id, projects.title, projects.start_time,
projects.description, projects.user_id, projects.winner_user_id,
user_owner.username as owner, user_winner.username as winner
FROM projects
inner join users user_owner on user_owner.user_id = projects.user_id
inner join users user_winner on user_winner.user_id = projects.winner_user_id
You first have the project, then you inner join on the owner (using one specific alias), and then inner join on the winner (using another specific alias).
And, in the select clause, you use those aliases to get the information you want -- same if you needed to restrict anything in a where clause, btw.
Note : if you also want projects which don't have a winner yet, you might want to use a left join instead of an inner.
Upvotes: 1
Reputation: 49354
SELECT ... FROM users AS winers, users AS owners
WHERE projects.user_id=owners.user_id
AND projects.winner_user_id=winners.user_id
Upvotes: 2
Reputation: 13916
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
Upvotes: 9