I've got 2 tables
我有2张桌子
event:
id| id_event| id_user | data
++++++++++++++++++++++++++++++++++
1 2 4 20.08.2012
2 2 5 20.08.2012
3 2 6 20.08.2012
rating:
id | id_event | id_user_who's rate | id_user_which get rate | rate
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 2 4 5
What I want to achieve is select users which took part in same event where id_user is equal to id_user_login and they were not been rated by him yet.
我想要实现的是选择参与同一事件的用户,其中id_user等于id_user_login并且他们尚未对他们进行评级。
So like on the example above id_user_login number 4 rated user number 5 so I need query which will give me back user 6
所以就像上面的例子id_user_login数字4评级用户数5所以我需要查询,这将给我回用户6
edit
$a = mysql_query("SELECT * FROM event as ev WHERE ev.id_user not in ( select $log1[id] union all SELECT id_user_whos_rate FROM event as e INNER JOIN rating as r on r.id_event = e.id_event WHERE e.id_user = $log1[id])
I've tried this one and as return I received all users from event table for id_user_login while one of user was already rated so he shouldn't be display.
我已经尝试了这个,作为回报,我从id_user_login的事件表中收到了所有用户,而其中一个用户已被评级,所以他不应该显示。
1 个解决方案
#1
0
SELECT a.id_user
FROM `event` a
LEFT JOIN rating b
ON a.id_event = b.id_event AND
a.id_user = b.id_user_which_get_rate
WHERE a.id_user <> 4 AND
EXISTS
(
SELECT 1
FROM `event` c
WHERE a.id_event = c.id_event AND
c.id_user = 4
) AND
b.id IS NULL
Table event is joined with table rating via columns: id_event and id_user using LEFT JOIN. It is joined on two columns inorder to select only rows that belong to a user in a particular event.
表事件通过列使用表等级加入:id_event和id_user使用LEFT JOIN。它连接在两列上,以便仅选择属于特定事件中用户的行。
The purpose of the WHERE clause is exclude the current user from the result list. The EXISTS filters only event for a particular current user who logged in.
WHERE子句的目的是从结果列表中排除当前用户。 EXISTS仅筛选登录的特定当前用户的事件。
#1
0
SELECT a.id_user
FROM `event` a
LEFT JOIN rating b
ON a.id_event = b.id_event AND
a.id_user = b.id_user_which_get_rate
WHERE a.id_user <> 4 AND
EXISTS
(
SELECT 1
FROM `event` c
WHERE a.id_event = c.id_event AND
c.id_user = 4
) AND
b.id IS NULL
Table event is joined with table rating via columns: id_event and id_user using LEFT JOIN. It is joined on two columns inorder to select only rows that belong to a user in a particular event.
表事件通过列使用表等级加入:id_event和id_user使用LEFT JOIN。它连接在两列上,以便仅选择属于特定事件中用户的行。
The purpose of the WHERE clause is exclude the current user from the result list. The EXISTS filters only event for a particular current user who logged in.
WHERE子句的目的是从结果列表中排除当前用户。 EXISTS仅筛选登录的特定当前用户的事件。