Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

join;MySQL

I have 3 tables, users (user info), mghours (volunteer time), mgtraining (training time). I want a result that lists some user columns, sum of volunteer hours and sum of two types of training:

uid lastname firstname mgstatus trainingyear vol at ce

I use the following and as long as I have the two fields from the mgtraining table and the LEFT OUTER JOIN for those in the query, I get skewed results. If I take that out of the query, my user info and vol columns are fine.

SELECT users.uid AS 'uid',
users.lastname AS 'lastname',
users.firstname AS 'firstname',
users.mgstatus AS 'mgstatus',
users.trainingyear AS 'trainingyear',
sum(mghours.hourstime) AS 'vol',
SUM(IF(mgtraining.trainingtype = 'AT', mgtraining.trainingtime, 0)) AS 'at',
SUM(IF(mgtraining.trainingtype = 'CE', mgtraining.trainingtime, 0)) AS 'ce'
FROM users
LEFT OUTER JOIN mghours ON users.uid = mghours.uid AND year(mghours.hoursdate) = YEAR(CURDATE())
LEFT OUTER JOIN mgtraining ON users.uid = mgtraining.uid AND year(mgtraining.trainingdate) = YEAR(CURDATE())
WHERE users.coid = 33
GROUP BY users.uid
ORDER BY users.lastname ASC

The users.coid = 33 references a county ID.

I've been pouring over this for hours. What am I doing wrong?

Best Answer

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect. To see this, remove the SUMs and GROUP BY and stare at the output. You may see, for example, the same mghours.hourstime showing up multiple times. Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps. Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Postgresql – How to inner join some tables and outer join others

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

SELECT *
FROM   users           u  
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Postgresql – How to inner join some tables and outer join others

Related Question