我有一个项目表,其中有两个用户外键(user_id和winner_user_id),一个用于项目所有者,一个用于项目的获胜者.就像是
+----------------+-------------------------+------+-----+---------+----------------+
| 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 | |
+----------------+-------------------------+------+-----+---------+----------------+
所以我制定了一个类似的查询
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
其中显然返回一个空集.真正的问题是如何引用这些不同的user_id.我甚至尝试使用AS关键字,然后使用我在同一个SQL查询中创建的名称,但显然这不起作用.
最后要说清楚我想要的东西
+------------+-------------------------------------------------+---------------------+---------+----------------+--------------+--------------+
| 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 |
如何构造查询来处理这个问题？
提前致谢.
最佳答案
你很近,但你需要加入用户表两次,一次是在所有者身上,一次是在获胜者身上.使用表别名来区分这两者.
SELECT
projects.project_id,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