MySQLのJOINとGROUP BY、COUNTを組み合わせた場合の出力がうまくいきません。


「仕事」と「申し込み」の以下のようなテーブルを結合して

▼tb_job(仕事)
|job_id|name|
|1|漁師|
|2|医者|
|3|農家|
|4|警官|

▼tb_contact(申し込み)
|contact_id|job_id|user|
|1|2|佐藤|
|2|2|田中|
|3|1|本村|

各仕事の申込数を以下のように出力したいと思っています。

▼理想
|job_id|name|申込数|
|1|漁師|1|
|2|医者|2|
|3|農家|0|
|4|警官|0|

そこで上記2つ「tb_contact」を「job_id」でGROUP BYして
SQLで以下のように結合するのですが

SELECT *,COUNT(*) FROM tb_contact RIGHT JOIN tb_job ON tb_contact.job_id = tb_job.job_id GROUP BY tb_contact.job_id;

下記のように申し込みがある仕事については集計できるのですが、
申し込みがない仕事については1括りにされてしまい、COUNTにも
その合計が表示されてしまいます。

▼結果
|contact_id|job_id|user|job_id|name|COUNT(*)
|NULL|NULL|NULL|3|農家|2
|3|1|本村|1|漁師|1
|1|2|佐藤|2|医者|2

「▼理想」のように出力するにはどの様なSQL文を投げれば良いのでしょうか。
ご教授のほど宜しくお願い致します。

回答の条件
  • 1人5回まで
  • 登録:
  • 終了:2012/03/09 07:58:52
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:khazad-Lefty No.3

回答回数181ベストアンサー獲得回数27

ポイント100pt

とりあえず、一番の原因はgroup by に 「tb_contactの」job_idを指定していることです。

tb_contactのjob_idに、3や4の値はないわけで、right joinした結果も、その値は当然nullです。
その結果「group by の値はnull」として、一列になってしまいます。

ということで、元のSQL文への変更を最小限にするなら

SELECT *,COUNT(*) 
FROM tb_contact 
RIGHT JOIN tb_job 
ON tb_contact.job_id = tb_job.job_id 
GROUP BY tb_job.job_id;

となります。

補足として、
group by または集計関数 を使用する時、Selectに「group by に指定されていない集計関数以外の列」を指定できてしまうのはMYSQLの独自拡張です。
今回も最初のSQLが「通ってしまう」ことが誤解の一員になっているんじゃないかと思います。
参考:寛容なMySQLを非寛容にすること(その3) | inquisitor

それをふまえた上で「理想」と同じ出力にするなら、こんな感じで。

SELECT tb_job.job_id,tb_job.name,COUNT(tb_contact.job_id) AS `申込数`
FROM tb_contact 
RIGHT JOIN tb_job 
ON tb_contact.job_id = tb_job.job_id 
GROUP BY tb_job.job_id,tb_job.name;

ちなみに、私の場合、

  1. JOINするテーブルには別名をつける(同じテーブルを複数JOINすることも多々あるため)
  2. RIGHT JOINは使用しない(RIGHT JOINはLEFT JOINに置き換え可能で、統一したほうが可読性が良くなるため)

というのを習慣にしているので、私が書くならこんな感じになります。

SELECT A.job_id,A.name,COUNT(B.job_id) AS `申込数`
FROM tbl_job AS A
LEFT JOIN tbl_contact AS B
ON A.job_id=B.job_id
GROUP by A.job_id,A.name;

その他の回答2件)

id:windofjuly No.1

回答回数2625ベストアンサー獲得回数1149

ポイント50pt

2例ほど・・・
データの量や内容によって速度が逆転したりするので、
実データで比較して選択する必要があります
(下記は、動作確認せず、いきなり書いています・・・)

(a)

SELECT a.job_id, a.name
    , (SELECT COUNT(*) FROM tb_contact b WHERE b.job_id = a.job_id) AS `申込数`
FROM tb_job a
;

(b)

SELECT a.job_id, a.name, COUNT(*) AS `申込数`
FROM tb_job a
LEFT JOIN  tb_contact b ON b.job_id = a.job_id
GROUP BY a.job_id, a.name
;
id:oil999 No.2

回答回数1728ベストアンサー獲得回数320

ポイント50pt

次のSQL文をお試しください。

SELECT tb_job.job_id,tb_job.name,count(tb_contact.job_id) AS '申込数'
FROM tb_contact RIGHT JOIN tb_job
ON tb_contact.job_id = tb_job.job_id
GROUP BY tb_job.job_id;
id:khazad-Lefty No.3

回答回数181ベストアンサー獲得回数27ここでベストアンサー

ポイント100pt

とりあえず、一番の原因はgroup by に 「tb_contactの」job_idを指定していることです。

tb_contactのjob_idに、3や4の値はないわけで、right joinした結果も、その値は当然nullです。
その結果「group by の値はnull」として、一列になってしまいます。

ということで、元のSQL文への変更を最小限にするなら

SELECT *,COUNT(*) 
FROM tb_contact 
RIGHT JOIN tb_job 
ON tb_contact.job_id = tb_job.job_id 
GROUP BY tb_job.job_id;

となります。

補足として、
group by または集計関数 を使用する時、Selectに「group by に指定されていない集計関数以外の列」を指定できてしまうのはMYSQLの独自拡張です。
今回も最初のSQLが「通ってしまう」ことが誤解の一員になっているんじゃないかと思います。
参考:寛容なMySQLを非寛容にすること(その3) | inquisitor

それをふまえた上で「理想」と同じ出力にするなら、こんな感じで。

SELECT tb_job.job_id,tb_job.name,COUNT(tb_contact.job_id) AS `申込数`
FROM tb_contact 
RIGHT JOIN tb_job 
ON tb_contact.job_id = tb_job.job_id 
GROUP BY tb_job.job_id,tb_job.name;

ちなみに、私の場合、

  1. JOINするテーブルには別名をつける(同じテーブルを複数JOINすることも多々あるため)
  2. RIGHT JOINは使用しない(RIGHT JOINはLEFT JOINに置き換え可能で、統一したほうが可読性が良くなるため)

というのを習慣にしているので、私が書くならこんな感じになります。

SELECT A.job_id,A.name,COUNT(B.job_id) AS `申込数`
FROM tbl_job AS A
LEFT JOIN tbl_contact AS B
ON A.job_id=B.job_id
GROUP by A.job_id,A.name;
  • id:gelgelgel
    皆さま、わかりやすく教えていただき有難うございました。
    とりあえず無事解決出来ました。詳しい内容についてはまた後ほどじっくり拝見させて頂きます。
    ありがとうございました。
  • id:jranar
    アホなこと書きますが、みんな凄いよね。
    7:20ごろ起きて、回答入ってなかったから書いてあげようとしたらわずか10分くらいの間に・・・ちょっとびっくりした。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません