複数JOINしているとCOUNTが正しく取得できな

質問

LAMP環境で開発をしています。
SQL文でCOUNTを求める際に、まとめて結果を求めようとして上手く行きません。
状況としては以下です。

テーブルdはidをkeyにa,b,c３つのテーブルとjoinしています。
id = 1の場合、テーブルa,b,cにマッチするレコードがそれぞれに４個、１個、０個あります。
ひとつひとつを

SELECT
COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count
FROM d
INNER JOIN a ON a.id = d.id
WHERE d.id = 1

として結果を求めると4,1,0と出るのですが、まとめて

SELECT
COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count,
COUNT(CASE WHEN b.name IS null THEN 1 ELSE null END) as b_count,
COUNT(CASE WHEN c.name IS null THEN 1 ELSE null END) as c_count
FROM d
INNER JOIN a ON a.id = d.id
INNER JOIN b ON b.id = d.id
INNER JOIN c ON c.id = d.id
WHERE d.id = 1

とすると28,5,0という値が返されます。
どのように書けば正しい4,1,0を得られるのでしょうか？

よろしくお願いします。

回答No.1

jjon-com · ベストアンサー

テーブルa内にid=1の行は5行存在。うち4行はnameがnullで，1行はname登録済。
テーブルb内にid=1の行は7行存在。うち1行はnameがnullで，6行はname登録済。
テーブルc内にid=1の行は1行存在。その1行はname登録済で，nameがnullは無し。

これを次のSQL文で結合すれば，
出力される行数は 5×7×1＝35行分。
SELECT *
FROM d
INNER JOIN a ON a.id = d.id
INNER JOIN b ON b.id = d.id
INNER JOIN c ON c.id = d.id
WHERE d.id = 1

そのうち，
a.nameがnullなのは(4)×7×1＝28行分，
b.nameがnullなのは5×(1)×1＝ 5行分，
c.nameがnullなのは5×7×(0)＝ 0行分。

> どのように書けば正しい4,1,0を得られるのでしょうか？

無意味にテーブルa,b,cを結合していること自体が正しくないわけですから，
テーブルa,b,cひとつひとつに対して結果を求めるのが自然だと思います。

お礼

ご明察です。
楽しようとすると逆にこういうことになってしまいますね。
ありがとうございました。

回答No.2

yambejp

たぶんこんな感じを期待しているのだとおもうけど
かならずnameがユニークだとは限らないためnameで判断するのは微妙

SELECT
count(DISTINCT a.name) as a_count
,count(DISTINCT b.name) as b_count
,count(DISTINCT c.name) as c_count
FROM d
LEFT JOIN a ON a.id = d.id
LEFT JOIN b ON b.id = d.id
LEFT JOIN c ON c.id = d.id
WHERE d.id = 1

お礼

参考にさせて頂きました。
ありがとうございました。