LEFT JOIN on the same table doubling rows

Question

I have two tables that I am trying to LEFT join but I am not getting the expected results.

Rooms have multiple Children on different days, however Children are only counted in a Room after they have started and if they have hours allocated on that day. The output I am trying to achieve is this.

Room  | MaxNum | Mon(Week1) | Tue(Week1) | Mon(Week2) | Tue(Week2)
Blue  | 5      | 4          | 4          | 3          | 2
Green | 10     | 10         | 10         | 9          | 9  
Red   | 15     | 15         | 15         | 15         | 15

Here is the schema and some data...

create table Rooms(
  id       INT,
  RoomName VARCHAR(10),
  MaxNum   INT
);

create table Children (
  id          INT,
  RoomID      INT,
  MonHrs      INT,
  TueHrs      INT,
  StartDate   DATE
);

INSERT INTO Rooms VALUES (1, 'Blue', 5);
INSERT INTO Rooms VALUES (2, 'Green', 10);
INSERT INTO Rooms VALUES (3, 'Red', 15);

INSERT INTO Children VALUES (1, 1, 5, 0, '2018-12-02');
INSERT INTO Children VALUES (2, 1, 0, 5, '2018-12-02');
INSERT INTO Children VALUES (3, 1, 5, 5, '2018-12-09');
INSERT INTO Children VALUES (4, 1, 0, 5, '2018-12-09');
INSERT INTO Children VALUES (5, 2, 5, 0, '2018-12-09');
INSERT INTO Children VALUES (6, 2, 0, 5, '2018-12-09');

The SQL I am having trouble with is this. It may not be the correct approach.

SELECT R.RoomName, R.MaxNum,
       R.MaxNum - SUM(CASE WHEN C1.MonHrs > 0 THEN 1 ELSE 0 END) AS Mon1,
       R.MaxNum - SUM(CASE WHEN C1.TueHrs > 0 THEN 1 ELSE 0 END) AS Tue1,
       R.MaxNum - SUM(CASE WHEN C2.MonHrs > 0 THEN 1 ELSE 0 END) AS Mon2,
       R.MaxNum - SUM(CASE WHEN C2.TueHrs > 0 THEN 1 ELSE 0 END) AS Tue2
  FROM Rooms R
        LEFT JOIN Children C1 
          ON R.id = C1.RoomID
         AND C1.StartDate <= '2018-12-02'     
        LEFT JOIN Children C2
          ON R.id = C2.RoomID
         AND C2.StartDate <= '2018-12-09'
 GROUP BY R.RoomName;

There is a double up happening on the Rows in the LEFT JOINs that is throwing the counts way off and I don't know how to prevent them. You can see the effect if you replace the SELECT with *

Any suggestions would help a lot.

This seems likely a faq where people need the join of multiple tables where some are aggregations (maybe of joins) but they try to do the aggregation(s) at the end. PS Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep. Make your post self-contained. — philipxy, Dec 19 '18 at 06:42
Possible duplicate of [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Dec 19 '18 at 06:43
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. (Thanks for the one you gave.) — philipxy, Dec 19 '18 at 06:46
If it was me , I'd start by amending the children table. Monhrs and tuehrs should be separate entries (rows) in the same column — Strawberry, Dec 19 '18 at 06:48
PS Absolute basics of debugging say: Show that your program does what you expect as it goes through (sub)expressions by saying what that is & showing that it actually does it via incremental output. On adding problem code that you can't fix, research (the manual & the web). Repeat, minimizing working & wrong code. Then ask re the (small) difference between working & non-working examples. (Here, you would see you *don't* want the sums from after your left joins. Also you could try to get your sums more simply--then see that you could join with other data.) — philipxy, Dec 19 '18 at 06:52

Tim Biegeleisen · Accepted Answer · 2018-12-19T07:11:36.243

This sort of problem usually surfaces from doing an aggregation in a too broad point in the query, which then results in duplicate counting of records. Try aggregating the Children table in a separate subquery:

SELECT
    R.RoomName,
    R.MaxNum,
    R.MaxNum - C.Mon1 AS Mon1,
    R.MaxNum - C.Tue1 AS Tue1,
    R.MaxNum - C.Mon2 AS Mon2,
    R.MaxNum - C.Tue2 AS Tue2
FROM Rooms R
LEFT JOIN
(
    SELECT
        RoomID,
        COUNT(CASE WHEN MonHrs > 0 AND StartDate <= '2018-12-02'
                   THEN 1 END) AS Mon1,
        COUNT(CASE WHEN TueHrs > 0 AND StartDate <= '2018-12-02'
                   THEN 1 END) AS Tue1,
        COUNT(CASE WHEN MonHrs > 0 AND StartDate <= '2018-12-09'
                   THEN 1 END) AS Mon2,
        COUNT(CASE WHEN TueHrs > 0 AND StartDate <= '2018-12-09'
                   THEN 1 END) AS Tue2
    FROM Children
    GROUP BY RoomID
) C
    ON R.id = C.RoomID;

Note that we can avoid the double left join in your original query by instead using conditional aggregation on the start date.

Late edit: You probably don't even need a subquery at all, q.v. the answer by @Salman. But either of our answers should resolve the double counting problem.

score 1 · Answer 2 · answered Dec 19 '18 at 07:05

1

You need to use one LEFT JOIN and move the date filter from JOIN condition to the aggregate:

SELECT R.id, R.RoomName, R.MaxNum
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-02' AND C.MonHrs > 0 THEN 1 END) AS Mon1
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-02' AND C.TueHrs > 0 THEN 1 END) AS Tue1
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-09' AND C.MonHrs > 0 THEN 1 END) AS Mon2
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-09' AND C.TueHrs > 0 THEN 1 END) AS Tue2
FROM Rooms R
LEFT JOIN Children C ON R.id = C.RoomID
GROUP BY R.id, R.RoomName, R.MaxNum

answered Dec 19 '18 at 07:05

Salman A

262,204
82
430
521

You probably only need `R.id` in the `GROUP BY` clause. – Tim Biegeleisen Dec 19 '18 at 07:12
@Tim older versions of MySQL will complain depending on ONLY_FULL_GROUP_BY setting. – Salman A Dec 19 '18 at 07:15
Thanks Salman. An equally correct answer. I will hang with @Tim's solution as after I add all the other bits in the SQL is a bit easier to read – KAHartle Dec 19 '18 at 08:14

LEFT JOIN on the same table doubling rows

2 Answers2