Anthony D Anthony D - 17 days ago 5
MySQL Question

Join two tables but exclude rows that match condition in a third table

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:

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:

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)

Answer

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)

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