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

user_1005858 0
Viewed 42

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?

count mysql sql
1 Answers

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;
user_22707620
Related