Re: LEFT JOIN WHERE GROUP BY

[irek kordirko]

Use an outer join (left or right) and a case expression,
in this way:

mysql> create table support_internalstatus(
    ->   id int,
    ->   name varchar(20)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into support_internalstatus values
    -> ( 1, 'Warranty' ), ( 2, 'Wrong item delivered' ), 
    -> (3, 'Exchanged'), ( 4, 'Warranty expired' );
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table support(
    ->   statusinternal_id int 
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into support values 
    -> ( 1 ), ( 1 ), 
    -> ( 2 ), ( 2 ), ( 2 ), ( 2 ),
    -> ( 3 ), ( 3 ), ( 3 ), ( 3 );
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT sum( case when s.statusinternal_id  is null then 0 else 1 end ) count, name
    -> FROM support s
    -> RIGHT JOIN support_internalstatus si
    -> ON si.id = s.statusinternal_id
    -> GROUP BY si.id 
    -> ;
+-------+----------------------+
| count | name                 |
+-------+----------------------+
|     2 | Warranty             |
|     4 | Wrong item delivered |
|     4 | Exchanged            |
|     0 | Warranty expired     |
+-------+----------------------+
4 rows in set (0.00 sec)