加入两个表，但排除第三个表中与条件匹配的行

I want to be able to search users by name and get results back for only for:

我希望能够按名称搜索用户并获取结果仅用于:

1. Those who I am friends with

那些我是朋友的人

2. Those who are NOT already aligned with my 'event'.

那些尚未与我的“事件”保持一致的人。

I have a query that does part #1 successfully:

我有一个成功完成第1部分的查询:

SELECT u.id, first_name, last_name
FROM user u
JOIN friend f
ON (
    ( f.requester_id = u.id AND f.recipient_id = :my_user_id )
    OR ( f.recipient_id = u.id AND f.requester_id = :my_user_id ) 
    AND confirmed = 1
)
AND (
    ( first_name LIKE :search_input OR last_name LIKE :search_input )
    OR ( CONCAT ( first_name, ' ', last_name ) LIKE :search_input )
)
AND NOT u.id = :my_user_id
ORDER BY last_name
LIMIT 5

Struggling to figure out how to add part #2. Tables look like this:

苦苦挣扎着弄清楚如何添加第2部分。表格如下所示:

USER
id | first_name | last_name
-- | ---------- | ---------
0  | John       | Doe
1  | Jane       | Doe
2  | Bob        | Smith
3  | Mike       | Jones

FRIEND
id | requester_id | recipient_id | confirmed
-- | ------------ | ------------ | ---------
0  | 0            | 1            | 1
1  | 3            | 1            | 1
2  | 2            | 3            | 1
3  | 1            | 2            | 1

USER_EVENT
id | user_id | event_id 
-- | ------- | --------
0  | 0       | 43                
1  | 0       | 846                 
2  | 0       | 1058              
3  | 1       | 846    
4  | 1       | 912                 
5  | 2       | 537                 
6  | 3       | 846 

Sample data a query might get:

查询可能获得的示例数据:

:search_input (e.g. '%bob%')
:my_user_id (e.g. 3)
:event_id (e.g. 846) 

1 个解决方案

#1

you can simply add another clause to your query: AND user_id NOT IN (SELECT user_id from USER_EVENT where event_id = :event_id)

您只需在查询中添加另一个子句:AND user_id NOT IN(USER_EVENT中的SELECT user_id,其中event_id =:event_id)

Full query:

SELECT u.id, first_name, last_name
FROM user u
JOIN friend f
ON (
    ( f.requester_id = u.id AND f.recipient_id = :my_user_id )
    OR ( f.recipient_id = u.id AND f.requester_id = :my_user_id ) 
    AND confirmed = 1
)
AND (
    ( first_name LIKE :search_input OR last_name LIKE :search_input )
    OR ( CONCAT ( first_name, ' ', last_name ) LIKE :search_input )
)
AND NOT u.id = :my_user_id
AND user_id NOT IN (SELECT user_id from USER_EVENT where event_id = :event_id)
ORDER BY last_name
LIMIT 5