CODEWITHSUNDEEP
mysqljoin
noru
noru

Reputation: 547

MySQL join - data not in second table

I have 3 tables, t1 containing events, t2 containing "attended" events, and t3 containing some invited user ids (linked to system user ids). More than one user can attend and event.

When a new event is created it's inserted into t1. Every time a user confirms attending an event a row is inserted in t2, otherwise the t2 table is empty.

The query bellow pulls all the events an user is attending.

SELECT * FROM events t1
INNER JOIN attending_events t2 ON t1.id_ev = t2.id_ev
INNER JOIN attending_users t3 ON t2.id_user = t3.id_user AND t3.id_system_user=17
WHERE t1.id_comp = 42

Please help, I need a query to do the exact opposite, find the events the user is not attending.

Upvotes: 3

Views: 1702

Answers (2)

Quassnoi
Quassnoi

Reputation: 425251

SELECT  e.*
FROM    events e
WHERE   e.id_ev NOT IN
        (
        SELECT  id_ev
        FROM    attending_events ae
        JOIN    attending_users au
        ON      au.id_user = ae.id_user
        WHERE   au.system_user_id = 17
        )
        AND id_comp = 42

or:

SELECT  *
FROM    events e
LEFT JOIN
(
        attending_events ae
JOIN    attending_users au
ON      au.id_user = ae.id_user
)
ON      ae.id_ev = e.id_ev
        AND au.system_user_id = 17
WHERE   e.id_comp = 42
        AND ae.id_ev IS NULL

Upvotes: 3

Taha Paksu
Taha Paksu

Reputation: 15616

select * from attending_events where id_ev not in 
(SELECT id_ev FROM events t1
INNER JOIN attending_events t2 ON t1.id_ev = t2.id_ev
INNER JOIN attending_users t3 ON t2.id_user = t3.id_user AND t3.id_system_user=17
WHERE t1.id_comp = 42)

first thing came to my mind. Definitely should be optimized.

Upvotes: 0

Related Questions