MySQL table join with COUNT column - where to place AND condition for count?

Question

I have 2 tables

Table_1:

| ID    | ARRIVAL    |   DEPARTED    | OTHER_COLUMNS |
| ----- | ---------- |-------------- |---------------
| 1     | 2022-07-24 |       0       |     data      |
| 2     | 2022-07-25 |       0       |     data      |
| 3     | 2022-07-28 |       0       |     data      |
| 4     | 2022-07-24 |       0       |     data      |

Table_2:

| ID    | DATE       | CONDITION | OTHER_COLUMNS |
| ----- | -------    | --------- |-------------- |
| 1     | 2022-07-24 | 0         |     data      |
| 2     | 2022-07-24 | 1         |     data      |
| 3     | 2022-07-24 | 1         |     data      |
| 4     | 2022-07-28 | 1         |     data      |

This MySQL query produces the result I expect:

    SELECT
    `Table_1`.`ID`,
    `Table_1`.`ARRIVAL`,
    `Table_2`.`DATE`,
    `Table_2`.`CONDITION`,
    
    COUNT(Table_2.DATE) AS My_COUNT

    FROM
    `Table_1`
                                                
    LEFT JOIN 
    `Table_2` 
    ON 
    `Table_1`.`ARRIVAL` = `Table_2`.`DATE`

WHERE `DEPARTED` = 0
                                            
    GROUP BY
    `Table_1`.`ID`

Result:

| ID    | ARRIVAL    |   My_Count    |
| ----- | ---------- |-------------- |
| 1     | 2022-07-24 |      3        |
| 2     | 2022-07-25 |               |
| 3     | 2022-07-28 |      1        |
| 4     | 2022-07-24 |      3        |

But I need a condition for the count:

AND `Table_2`.`CONDITION` = 1

I want to produce the result:

| ID    | ARRIVAL    |   My_Count    |
| ----- | ---------- |-------------- |
| 1     | 2022-07-24 |      2        |
| 2     | 2022-07-25 |               |
| 3     | 2022-07-28 |      1        |
| 4     | 2022-07-24 |      2        |

But I do not know where to place my "AND" statement.

When I place it with the join:

LEFT JOIN 
`Table_2` 
ON 
`Table_1`.`ARRIVAL` = `Table_2`.`DATE`

    WHERE `DEPARTED` = 0
    AND `Table_2`.`CONDITION` = 1

Not all rows are returned from Table_1 - yet the My_Count will be accurate.

I need all of the rows from Table_1

How do I place my 'AND' Statement so that my query will return all of the rows from Table_1 with the accurate count column?

The condition belongs in the `ON` clause, because you want to outer-join condition-1 rows. If you put it in `WHERE` instead, your outer joined rows where the condition column is set to null, get removed and you are back to a mere inner join. — Thorsten Kettner, Jun 27 '22 at 22:22

score 1 · Accepted Answer · answered Jun 27 '22 at 22:19

Your query is invalid. Why doesn't MySQL throw an error? I suppose you are working in MySQL's notorious cheat mode. Make sure to set

SET sql_mode = 'ONLY_FULL_GROUP_BY';

to prevent MySQL from surpressing such errors. You group by table1.id, but select table2.condition. As you know yourself there is not one table2.condition per table1.id, so what condition do you mean? The DBMS must raise an error on such invalid queries. I am pretty sure that MySQL has defaulted to ONLY_FULL_GROUP_BY for quite some time. As this doesn't seem to be the case for you, this may mean that you are working with a very old MySQL version. In that case you should consider an upgrade.

As to the query: You want to outer join condition-1 rows, so the criteria belongs in the join condidtion:

LEFT JOIN table_2 ON table_2.date = table_1.arrival AND table_2.condition = 1

I suggest, though, you make it a habit to aggregate before joining. This can prevent errors later when you are working with different aggregates.

SELECT
  t1.*, COALESCE(my_count, 0) AS date_count
FROM table_1 t1
LEFT JOIN
(
  SELECT date, COUNT(*) AS my_count
  FROM table_2
  WHERE condition = 1
  GROUP BY date
) t2 ON t2.date = t1.arrival
WHERE t1.departed = 0
ORDER BY t1.id;

Thank you. I find everything is clear in your answer The brackets and the 'criteria belongs in the join' makes good sense to me, and is where I was applying my efforts to resolve this. - I just wish that there were a way to create the date_count column without the 'COALESCE'. The date_count seems detached to me and I was not looking for such logic, as the logic is not obvious to me. I would expect the 'COUNT(*)' in the LEFT JOIN with the brackets to be my criteria that displays the count column. Is there a more visually obvious way to achieve this? — TV-C-1-5, Jun 28 '22 at 00:32
No, that's the drawback of aggregating before joining. As we outer join the aggregated result, the count will be null when there is no match and we must replace the null with zero (unless you are fine with a null). On the other hand, joining the aggregated data makes the intention clear (join daily counts to the arrivals), may prevent from building an unnecessarily big intermediate result and is less prone to errors when you want to add aggregations from other tables to your query. — Thorsten Kettner, Jun 28 '22 at 04:44

MySQL table join with COUNT column - where to place AND condition for count?

1 Answers1