データの抽出およびデータ件数

質問

こんにちは。お世話になっております。
お忙しい中恐縮ですが、アドバイスを頂戴出来ればと投函させていただきます。

siwake：
no　id　group_type
1　　2　　未分類
2　　1　　未分類
3　　1　　売上
4　　1　　仕入
5　　2　　売上
6　　1　　仕入

group_select：
no　id　group_type
1　　1　　未分類
2　　1　　売上
3　　1　　仕入
4　　2　　未分類
5　　2　　売上

以上のようなテーブル（shiwakeは日々蓄積されるデータ。group_typeは基本となるべく区分用データ）があり（各idはそれぞれのデータを紐付けるためのもの）、下記のようにgroup_selectにてidを検索した際、group_typeを表示、かつshiwakeテーブル内にある、同じidで登録のある、group_typeの件数を表示させたく考えてます。

id=1を検索
group_type　件数
未分類　　　1
売上　　　　1
仕入　　　　2

そこで、これまで検索に関し、多くのアドバイスを頂いていることから下記コードを記述してみましたが、エラーもないまま結果が返ってきません。
毎度お恥ずかしい質問で恐縮ですが、今一度ご指導いただければと、投函させていただきました。
お忙しい中恐縮ですが、宜しくお願い申し上げます。

$sql = "select FG.`no`, FG.`id`, FG.` group_type`, F.`no`, F.`id`, count(F.`no`) as `count`
from `siwake` AS F
right join `group_select` AS FG on FG.`no` = F.`id`
where FG.`id` = '$id'
group by FG.`no`
order by FG.`no` desc";

回答No.4

chukenkenkou · ベストアンサー

見逃してしまっていたのですが、このSQLで、siwake表のno列を得てはだめです。

標準SQLや殆どのRDBMSでは、group by指定時にselectの選択リストで指定できるのは、group byで指定した列か、集計（集合）関数だけです。
MySQLでは、この部分に拡張仕様を持っていて、group byでグループ化した結果、一意になるなら、group byで指定していない列を指定できるようになっています。
もし、一意にならないなら、結果は保証されません。

http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html?ff=nopfpls

例を示すと、次のようになります。

＜例１＞
select c1,count(*) from t1 group by c1
→どのRDBMSでも指定可能

＜例２＞
select c1,c2,count(*) from t1 group by c1
→c2を指定することは、標準SQLでは違反であり、殆どのRDBMSでは文法エラーになる。
MySQLではこの指定は文法エラーにならず、「group by c1」でc2も一意になるなら指定してよい。一意にならないなら結果は保証しない。

お礼

chukenkenkou様

こんばんは。度重なるご指導を有難う御座います。
回答いただいた直後から、お教えいただいた内容の理解を深めようとしておりましたが、いまいち理解に乏しく、ならば別々にgroup_typeの種類、件数を取得して配列に・・・と回り道？などと思いながらも、試行錯誤を繰り返しておりますが、php側で一向に上手くいく気配が見れずに居る次第です。

chukenkenkou様のおっしゃるように、今回のケースは上手く行かないのでしょうか・・・すみません。折角アドバイスを頂いているにも関わらず理解し切れておりません。

ちなみに、現在のSQL文は以下のようになっており、shiwakeテーブルに登録のないデータでも、group_selectテーブル内にあるグループの表示までは出来ましたが、全て同じ件数（一番多い件数）となっております。

ご指摘下さった件に関して理解が乏しく、自分なりに歯痒いながらも、ここまで来ているのに・・という思いです。
説明不足なところも多々ありますが、引続きアドバイスを頂戴出来れば幸いです。お忙しい中恐縮ですが、宜しくお願いいたします。

$sql = "select FG.`no`, FG.`id`, FG.`group_type`, count(F.`id`) as `count`
from `siwake` AS F
right join `group_select` AS FG on FG.`id` = F.`id`
where FG.`id` = '$id'
group by FG.`group_type`
order by F.`no`";

補足

chukenkenkou様

こんばんは。お世話になっております。
お騒がせした箇所ですが、これまで多くの方にアドバイスいただいたソースを参考に、試行錯誤ながら以下のソースにて解決出来ました。
色々とご指導いただき感謝しております。有難う御座いました。

$sql = "select FG.`no`, FG.`id`, FG.`group_type`, F.`id`, count(F.`group_type`) as `count`
from `siwake` AS F
right join `group_select` AS FG on FG.`group_type` = F.`group_type`
where FG.`id` = '$id' AND F.`id` = '$id'
group by FG.`group_type`
order by F.`no`";

回答No.3

chukenkenkou

最新のSQLは、どういうSQLになっているのでしょうか？

質問で提示されたSQLに、#1で指摘した内容を反映したSQLならば、siwakeにないデータ（'売掛'なのか'買掛'なのか分かりませんが。。。）も、0件として表示されるはずですが？

回答No.2

calltella

siwake：
no　id　group_type
1　　2　　未分類
2　　1　　未分類
3　　1　　売上
4　　1　　仕入
5　　2　　売上
6　　1　　仕入

group_select：
no　id　group_type
1　　1　　未分類
2　　1　　売上
3　　1　　仕入
4　　2　　未分類
5　　2　　売上

上記のsiwakeテーブルにもgroup_selectテーブルにも
一意となる値がどこにもありません。

つまりsiwakeの1レコード目を例に取ると
[no:1 id:2 group_type:未分類]
これをgroup_selectテーブルから特定しようとすると
[id:2 group_type:未分類]の２つのキーが必要になります。
キーが２つでもご希望の様な事は可能ですが汎用性を考えるとテーブルを作成し直した方が楽です。

以上を踏まえてテーブルを再作成

siwake：
id　group_type
4　　未分類
1　　未分類
2　　売上
3　　仕入
5　　売上
3　　仕入

group_select：
no　group_type
1　　未分類
2　　売上
3　　仕入
4　　未分類
5　　売上

以下のＳＱＬを実行
select group_select.group_type,count(*) from siwake
left join group_select on siwake.id=group_select.no
group by group_select.no

・・・・と、ここまで書いて結果が違うので気づいたのですが
「group_selectにてidを検索した際、group_typeを表示、かつshiwakeテーブル内にある、同じidで登録のある、group_typeの件数」
ってのを見逃しておりました。

それなら
「SELECT group_type,count(no) FROM `siwake`　where id='1' group by group_type」
↑これでご希望の結果になりますが？

お礼

calltella様

はじめまして、こんばんは。順を追ってのご説明を有難う御座います。
投函時の説明不足だったのですが、下記のようなテーブル構成があった際、group_typeテーブルに登録がある分類がshiwakeテーブルになかった場合は、0とカウント表示させたく思っております。

siwake：
no　id　group_type
1　　2　　未分類
2　　1　　未分類
3　　1　　売上
4　　1　　仕入
5　　2　　売上
6　　1　　仕入

group_select：
no　id　group_type
1　　1　　未分類
2　　1　　売上
3　　1　　仕入
4　　2　　未分類
5　　2　　売上
6　　1　　売掛

結果：id=1 で検索した場合
未分類　1
売上　　1
仕入　　2
売掛　　0

当初回答を頂いてから、試行錯誤を繰り返していますが、思うような結果を未だ得られずにおります。お忙しい中恐縮ですが、引続きご指導いただければ幸いです。

回答No.1

chukenkenkou

やりたいと言っていることと、SQLでやろうとしていることが合っていませんが？

このSQLは、コードをコピー＆ペーストしたものですか？そうであれば、構文エラーですけど？
「select FG.`no`, FG.`id`, FG.` group_type`, F.`no`, F.`id`, count(F.`no`) as `count`」
→「FG.` group_type`」の部分で、列名の先頭に空白が入っています。

やりたいことと、SQLが違っている部分は、次の通り。
(1)「order by FG.`no` desc」→「order by FG.`no`」では？
(2)「on FG.`no` = F.`id`」→「on FG.`id` = F.`id`」
(3)(2)に加え、id列だけでは行を一意に絞り込めないので、「on FG.`id` = F.`id` and FG.`group_type`=F.`group_type`」の条件が必要。

お礼

chukenkenkou様

こんばんは。いつもお世話になっております。
早速のアドバイスを有難う御座います。

ご指摘の、1、2は私のミスで、投函後に修正を済ませておりましたが、お蔭様をもちまして、3の記述で無事検索結果を返すことが出来ました。

そこで、追加質問のようで恐縮ですが（単に投函する際の説明不足）、
group_selectテーブルにおいて登録済みのデータが、日々登録のsiwakeテーブルにデータがない場合、検索結果で、

group_type　件数
未分類　　　1
売上　　　　1
仕入　　　　2
買掛　　　　0

のように、group_selectにデータがあるが、siwakeにはデータがない場合は、０を表示させるにはどのようにしたら良いのでしょうか？
度重なる質問で恐縮ですが、引続きご指導頂けたら幸いです。お忙しい中恐縮ですが宜しくお願いいたします。