SQLのCOUNTで0件だった場合も取得できるようにしたい

Question

前提・実現したいこと

店舗ごとの会員数、アプリ会員数を算出するSQLを書きました。
会員数、アプリ会員数両方とも1件以上ないと店舗が取得できないのですが、
本来は0件だった場合も取得できるようにしたいです。

この場合はどのように書けばよいでしょうか？PostgreSQLです。

発生している問題・エラーメッセージ

0件のデータが取れない

該当のソースコード

select "ms"."name", COUNT(DISTINCT mc1.id) as clients_num, COUNT(DISTINCT mc2.id) as app_clients_num
from "m_shop" as "ms"
inner join "m_client" as "mc1" on "mc1"."shop_id" = "ms"."id"
inner join "m_client" as "mc2" on "mc2"."shop_id" = "ms"."id"
inner join "sales" as "sa" on "sa"."client_id" = "mc1"."id"
where "mc2"."mobileapp" = 1
and "sa"."settlement_date"::date >= '2021-07-01'
and "ms"."deleted_at" is null
group by "ms"."name";

Answer 1

内部結合(inner join)しているから、0件のデータは取得されません。
外部結合(outer join)しましょう。

select ms.name
     , COUNT(DISTINCT mc1.id) as clients_num
     , COUNT(DISTINCT mc2.id) as app_clients_num
from  m_shop as ms
      left join m_client as mc1
      on    mc1.shop_id = ms.id
      left join m_client as mc2
      on    mc2.shop_id = ms.id
        and mc2.mobileapp = 1
      left join sales as sa 
      on    sa.client_id = mc1.id
        and sa.settlement_date::date >= '2021-07-01'
where ms.deleted_at is null
group by  ms.name

※mc2への結合はmc1との結合と違いがありませんが、誤記ではないですか？
※項目名は全て小文字の様ですから、"で括るのは大文字を識別させたい時のみですので、必要はありません。

追記

select ms.name
     , COUNT(mc.id) as clients_num
     , COUNT(case when mc.mobileapp = 1 then mc.id end) as app_clients_num
from  m_shop as ms
      inner join sales as sa 
      on    sa.client_id = ms.id
      left join m_client as mc
      on    mc.shop_id = ms.id
where ms.deleted_at is null
  and sa.settlement_date::date >= '2021-07-01'
group by  ms.name

Comments

[mediaquery]

ご丁寧にありがとうございます。
理想にだいぶ近い形で取得できました。

> ※mc2への結合はmc1との結合と違いがありませんが、誤記ではないですか？
今回同じテーブルから違う条件でCOUNTするためにmc1とmc2を結合しました。
`mc2.mobileapp = 1` があることで、この条件のCOUNTが取れるのかなと思い。
他に書き方があれば知りたいです。

> ※項目名は全て小文字の様ですから~~~
ありがとうございます。仰る通りでした。

またご教授いただいたSQLで取得しましたが、
`clients_num` については、 `and sa.settlement_date::date >= '2021-07-01'` この条件が効いてないような挙動が見られました。これはleft joinでNULLでも結合されているからでしょうか？

[sazi]

> `mc2.mobileapp = 1` があることで、この条件のCOUNTが取れるのかなと思い、他に書き方があれば知りたいです。

count()の際に条件判定するのが良いかと思います。

> `clients_num` については、 `and sa.settlement_date::date >= '2021-07-01'` この条件が効いてないような挙動が見られました。

結局、m_shop に対する条件となるようなので、m_shop とinner join するのが効率的ですね。

[mediaquery]

ご丁寧にありがとうございました。
無事解決することができました。