CODEWITHSUNDEEP
mysqlsql
FooBar
FooBar

Reputation: 6128

MySQL do INNER JOIN if specific value is 1

I have this query:

SELECT 
    e.*, u.name AS event_creator_name 
FROM `edu_events` e 
    LEFT JOIN `edu_users` u 
        ON u.user_id = e.event_creator 
    INNER JOIN `edu_event_participants` 
        ON participant_event = e.event_id && participant_user = 1 
WHERE 
    MONTH(e.event_date_start) = 6 
    AND YEAR(e.event_date_start) = 2013

It works perfect, however, I only want to do the INNER JOIN if the value: e.event_type equals 1. If not, it should ignore the INNER JOIN.

I have tried for some time to figure it out, but the solutions seems difficult to implment for my proposes (as it is only for select/specific values).

I'm thinking about something like:

SELECT 
    e.*, u.name AS event_creator_name 
FROM `edu_events` e 
    LEFT JOIN `edu_users` u ON u.user_id = e.event_creator

if(e.event_type == 1) {
    INNER JOIN `edu_event_participants` ON participant_event = e.event_id && participant_user = 1 
}

WHERE MONTH(e.event_date_start) = 6 
AND YEAR(e.event_date_start) = 2013

Upvotes: 0

Views: 3659

Answers (3)

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

I have edited the below following further feedback from @Matthias

-- This will get all events for a given user plus all globals
SELECT 
    e.*, 
    u.name AS event_creator_name 
FROM `edu_users` u 

-- in the events
INNER JOIN `edu_events` e 
    ON (
        -- Get all the ones that the user is participant
        e.event_creator = u.user_id
        -- Or where event_type is 1
        OR 
        e.event_type = 1
    )
    AND e.event_date_start BETWEEN DATE('2013-06-01') AND DATE('2013-07-01')
    
-- Add in event participants even though it doesn't seem to be used?
INNER JOIN `edu_event_participants` AS eep 
    ON eep.participant_event = e.event_id
    AND eep.participant_user = 1
    
-- Add the user ID into the WHERE
WHERE u.user_id = 1;

This just might not make too much sence as it feels as though edu_event_participants has too much information in. event_creator should really be stored against the event itself, and then event_participants just containing an event id, user id, and user type.

If you are looking to get all users on an event, it may be better to do a seperate query for that event to select all users based off an event_id

The note on your use of MONTH() and YEAR(). This will trigger a table scan, as MySQL will need to apply the MONTH() and YEAR() functions to all rows to determine which match that WHERE statement. If you instead calculate the upper and lower limits (i.e. 2013-06-01 00:00:00 <= e.event_date_start < 2013-07-01 00:00:00) then MySQL can use a far more efficient range scan on an index (assuming one exists on e.event_date_start)

Upvotes: 1

jvilhena
jvilhena

Reputation: 1131

If I understand correctly you only want the results where there is an entry on edu_event_participants with the same event_id and participant_user = 1 but only if event_type = 1, but you don't really want to get any information from the edu_event_participants table. If that is the case:

SELECT 
    e.*, u.name AS event_creator_name 
FROM `edu_events` e 
    LEFT JOIN `edu_users` u 
        ON u.user_id = e.event_creator 
WHERE 
    -- as Simon at mso.net suggested
    WHERE e.event_date_start BETWEEN DATE('2013-06-01') AND DATE('2013-07-01')
    -- MONTH(e.event_date_start) = 6 
    -- AND YEAR(e.event_date_start) = 2013
    AND (
        -- either event is public
        e.event_type = 1 or
        -- or the user is in the participants table
        exists
            (select 1 from `edu_event_participants` 
            where participant_event = e.event_id
            AND participant_user = 1)
    )

Upvotes: 0

gerrytan
gerrytan

Reputation: 41133

Maybe what you're after is displaying the left table value even if there's no matching data from right table? On that case you can use outer join like so:

LEFT OUTER JOIN `edu_event_participants` ON participant_event = e.event_id && participant_user = 1 AND e.event_type = 1

Upvotes: 0

Related Questions