MySql countの結果が０でも表示する方法

2019.12.26
mysql
mysql

とあるテーブル内をデータ集計時に、マスタデータ側ではidに対して利用できるマークの様なものがあり、実際に利用しているマークを管理するテーブルで、まだ利用されていないマークはcount結果が０となり、その結果も取得できるクエリの作詞依頼があり、少し苦戦したのでクエリを記載。

1. 再現テーブル作成

再現テーブル作成

mtb_use → idに対して利用できるマークを管理するテーブル

create table mtb_use
(id smallint,mark varchar(2));

insert into mtb_use values(1,'○');
insert into mtb_use values(1,'×');
insert into mtb_use values(2,'◎');
insert into mtb_use values(2,'○');
insert into mtb_use values(2,'△');
insert into mtb_use values(2,'×');

create table mtb_use

(id smallint,mark varchar(2));

insert into mtb_use values(1,'○');

insert into mtb_use values(1,'×');

insert into mtb_use values(2,'◎');

insert into mtb_use values(2,'○');

insert into mtb_use values(2,'△');

insert into mtb_use values(2,'×');

tbl_result → idで実際に利用しているマークを管理するテーブル

create table tbl_result
(id smallint,name varchar(8),mark varchar(2));

insert into tbl_result values(1,'tom','○');
insert into tbl_result values(1,'jim','○');
insert into tbl_result values(2,'tanaka','×');
insert into tbl_result values(2,'yamada','×');
insert into tbl_result values(2,'kitamura','×');
insert into tbl_result values(2,'yokokawa','◎');
insert into tbl_result values(2,'hirai','△');

create table tbl_result

(id smallint,name varchar(8),mark varchar(2));

insert into tbl_result values(1,'tom','○');

insert into tbl_result values(1,'jim','○');

insert into tbl_result values(2,'tanaka','×');

insert into tbl_result values(2,'yamada','×');

insert into tbl_result values(2,'kitamura','×');

insert into tbl_result values(2,'yokokawa','◎');

insert into tbl_result values(2,'hirai','△');

mtb_use

mysql> select * from mtb_use;
+------+------+
| id   | mark |
+------+------+
|    1 | ○    |
|    1 | ×    |
|    2 | ◎    |
|    2 | ○    |
|    2 | △    |
|    2 | ×    |
+------+------+

mysql> select * from mtb_use;

+------+------+

| id | mark |

+------+------+

| 1 | ○ |

| 1 | × |

| 2 | ◎ |

| 2 | ○ |

| 2 | △ |

| 2 | × |

+------+------+

tbl_result

select * from tbl_result;
+------+----------+------+
| id   | name     | mark |
+------+----------+------+
|    1 | tom      | ○    |
|    1 | jim      | ○    |
|    2 | tanaka   | ×    |
|    2 | yamada   | ×    |
|    2 | kitamura | ×    |
|    2 | yokokawa | ◎    |
|    2 | hirai    | △    |
+------+----------+------+

select * from tbl_result;

+------+----------+------+

| id | name | mark |

+------+----------+------+

| 1 | tom | ○ |

| 1 | jim | ○ |

| 2 | tanaka | × |

| 2 | yamada | × |

| 2 | kitamura | × |

| 2 | yokokawa | ◎ |

| 2 | hirai | △ |

+------+----------+------+

この2つテーブルから、下記の結果を抽出する

+---------------------------------------+-------------------------------------+
| mtb_use　id=2 で利用可能なmark         | tbl_resule id=2 実際利用されているmark |
+---------------------------------------+-------------------------------------+
| ×                                     |               3 |
| ◎                                    |               1 |
| △                                    |               1 |
| ○                                     |               0 |
+---------------------------------------+-------------------------------------+

+---------------------------------------+-------------------------------------+

| mtb_use　id=2 で利用可能なmark | tbl_resule id=2 実際利用されているmark |

+---------------------------------------+-------------------------------------+

| × | 3 |

| ◎ | 1 |

| △ | 1 |

| ○ | 0 |

+---------------------------------------+-------------------------------------+

使用したクエリは下記となります。

select m.mark ,count(t.mark) as count
from mtb_use as m
left join tbl_result as t on m.mark=t.mark and t.id = 2
where m.mark in (select mark from mtb_use WHERE id =2) and m.id =2
group by m.mark;