CODEWITHSUNDEEP
phpmysqlleft-join
Saad Imran.
Saad Imran.

Reputation: 4530

mysql left joining same table twice

I'm trying to left join the same table twice but it's giving me some problems. I have two tables ee_all and ee_calendar_events that I want to join.

SELECT
    u.first_name,
    u.last_name,
    u.email,
    SUM(e1.total_vacation_hours_earned) AS vacation_hours_earned,
    SUM(e2.absent_hours)
FROM ee_all AS u 
LEFT JOIN ee_calendar_events AS e1 ON u.user_id = e1.sched_user_id
LEFT JOIN ee_calendar_events AS e2 ON u.user_id = e2.sched_user_id AND e2.event_id = 2
WHERE
    u.user_id = 23

The vacation_hours_earned column is supposed to return 133, which it does if I take out the second join. But soon as I add it, the query takes forever and the vacation_hours_earned has a value of 2000 or something (which is wrong). My guess is it's summing the row again when I add the second join, but I don't wan't that. I've been trying for a few hours but can't find a way around it, would appreciate any help.

Upvotes: 1

Views: 2538

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Using some MySQL syntax, you can just eliminate the second left join and simplify the query;

SELECT
    u.first_name,
    u.last_name,
    u.email,
    SUM(e1.total_vacation_hours_earned) AS vacation_hours_earned,
    SUM(e1.absent_hours * (event_id=2))
FROM ee_all AS u 
LEFT JOIN ee_calendar_events AS e1 ON u.user_id = e1.sched_user_id
WHERE
    u.user_id = 23

Demo here.

Upvotes: 3

Jatin
Jatin

Reputation: 1708

SELECT
    u.first_name,
    u.last_name,
    u.email,
    SUM(e1.total_vacation_hours_earned) AS vacation_hours_earned,    
   (select SUM(e2.absent_hours) as absenthours from ee_calendar_events AS e2 where u.user_id = e2.sched_user_id AND e2.event_id = 2)    
    FROM ee_all AS u 
    LEFT JOIN ee_calendar_events AS e1 ON u.user_id = e1.sched_user_id
    WHERE
    u.user_id = 23

Upvotes: 0

Hammerite
Hammerite

Reputation: 22340

When the rightmost table (the second join) has more than one row corresponding to a row of the table expression to the left, rows of the left-hand table expression are duplicated and count more than once in the SUM. Use subqueries instead.

SELECT
    u.first_name,
    u.last_name,
    u.email,
    (
        SELECT
            SUM(e1.total_vacation_hours_earned)
        FROM
            ee_calendar_events AS e1
        WHERE
            u.user_id = e1.sched_user_id
    ) AS vacation_hours_earned,
    (similar) AS absent_hours
FROM
    ee_all AS u
WHERE
    u.user_id = 23

Upvotes: 3

Related Questions