Ric Ric - 10 months ago 40
MySQL Question

Perform an INNER JOIN only if the table being joined contains a value

I have the following query which gets the

user_id
from the table
profiles
where the profile.status is wip/declined/cancelled and where their user_activity_log.last_activity time was more than 1 minute ago:

SELECT t1.user_id
FROM profiles t1
INNER JOIN user_activity_log t2
ON t1.user_id = t2.user_id
WHERE t1.status IN ('wip','declined','cancelled')
AND t2.last_activity < NOW() - INTERVAL 1 MINUTE


This works as expected however what I want to do now is include this:

INNER JOIN email_logs t3
ON t1.user_id = t3.user_id AND t3.sent_datetime < NOW() - INTERVAL 1 MINUTE


But I only want this join to happen if the
user_id
exists in that table (
email_logs
) otherwise return the results from the initial query.

How would I do this?

Many thanks

Answer Source

You can do this with NOT EXISTS, but the condition in the subquery changes slightly:

SELECT p.user_id 
FROM profiles p INNER JOIN
     user_activity_log ual
     ON p.user_id = ual.user_id 
WHERE p.status IN ('wip', 'declined', 'cancelled') AND
      ual.last_activity < NOW() - INTERVAL 1 MINUTE AND
      NOT EXISTS (SELECT 1
                  FROM email_logs el
                  WHERE p.user_id = el.user_id AND
                        el.sent_datetime >= NOW() - INTERVAL 1 MINUTE
                 )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download