結合したテーブルの特定カラムのカウントについて

質問

PHPを学習しており、MySQLでデータベースにPDOで接続をしています。

添付画像のようなチームのデータを管理するテーブル(teamテーブル)と
メンバーのデータを管理するテーブル(memberテーブル)があり、
teamテーブルのkeywordカラムを検索条件にし、
keywordカラムの値が『keyword1』と一致するteamテーブルのレコードの
teamcodeカラムの値と一致する値を持つ
memberテーブルのレコード数をカウントして
teamテーブルから『teamcode』、『teamname』、『teamkana』、『date』を取得し、memberテーブルでの『teamcode』の該当数を取得したいです。

OUTER JOINして、NULLIFやCOALESCEなどを使用して試してみたのですが、
構文の使い方が悪いのか、そもそも使うべきSQL文が違うのかわかりませんが、
添付画像の『teamcode』の値が『0004』のもののように
memberテーブルに該当する値が存在しないものは
NULLになってしまい、取得ができませんでした。
できればmemberテーブルに該当する値が存在しない場合は「0」として取得したいです。

このような場合、どのようなSQL文を記述したら良いのでしょうか？
少しややこしい内容ですので、説明が不足していたり、
説明が分かりづらいようであれば補足します。
ご回答、よろしくお願いします。

回答No.1

yambejp · ベストアンサー

こんな感じ？（一部省略）

//元データ
create table team(teamcode int(4) unsigned zerofill not null unique,teamname varchar(20));
insert into team values(1,'team1'),(2,'team2'),(3,'team3'),(4,'team4'),(5,'team5');
create table member(membercode int(4) unsigned zerofill not null unique,teamcode int(4) unsigned zerofill,membername varchar(20));
insert into member values(1001,1,'member1'),(1002,2,'member2'),(1003,3,'member3'),(1004,2,'member4');

//検索
select t1.teamcode,t1.teamname,count(t2.membercode) as gaitou
from team as t1
left join member as t2 on t1.teamcode=t2.teamcode
group by t1.teamcode

お礼

ご回答ありがとうございます！
GROUP BYの前にWHERE句を挿入したところバッチリ欲しい結果が取得できました！
すごく困っていたので感謝です！