グループ化したテーブルと他のテーブルの結合について

質問

お世話になります。PHP+MySQLでシステムを作っています。

商品のカテゴリーのテーブル・・・hametome_category（主キー：category_id ）
商品のテーブル・・・hametome_item（主キー：item_id ）
注文のテーブル・・・hametome_order（主キー：order_id ）

上記の3つのテーブルがあります。各テーブルにはそれぞれ、下記のようなデータを格納していて（）内のカラムが含まれています。

hametome_category・・・カテゴリー名（category_id）
hametome_item・・・商品名、単価など（category_id、item_id）
hametome_order・・・注文内容等（category_id、item_id）

この3つのテーブルを使い、カテゴリーごとの商品の一覧表を作り、一覧表の中に商品ごとの注文数量の累計を表示させたいと思っています。

【作りたい表のイメージ図】
　　　　単価　在庫数　注文数累計
商品A　 100 1500 11000
商品B　 120 1000 13000
商品C　 130 1700 18000
　・
　・
　・

表がずれていたらスイマセン＾＾；

SELECT文を使ってデータを抽出するのですが、この際、

$sql="select * from hametome_category c,hametome_item i where i.category_id = $id and c.category_id = i.category_id ORDER BY item_id";

このようにすると任意のカテゴリーに属する商品の一覧表を出力する事は出来るのですが（$idは前ページからPOSTされてきます。）、これに注文内容のデータ抽出を組み合わせようとするとうまくいきません。

この表に注文内容（hametome_orderに登録されているデータ）をitem_idでグループ化し合計したものを表示させたいです。

$sql="select *,SUM(order_amount) AS goukei from hametome_order GROUP BY item_id";

とすれば、注文数の累計をitem_idで合計しグループ化する事は出来るのですが（『order_amount』はhametome_orderテーブルに登録されている注文数です。）、この内容を最初のSELECT文にうまく組み込む事が出来ません。

どのようにすれば思うような結果を得られるでしょうか？ご教授のほどよろしくお願い申し上げます。

回答No.2

chukenkenkou · ベストアンサー

>のように書くとどうもうまくいきません。

「うまくいかない」といった漠然とした説明でなく、具体的なエラーメッセージ等を提示するようにしてください。

実機確認していませんが、インラインビューはこんな感じでは？

(select item_id,category_id,SUM(order_amount) as goukei
from hametome_order GROUP BY item_id) AS o

なお、MySQLでは上記の書き方でも文法エラーになりませんが、標準SQLや他の主要なRDBMSでは許されません。
→group byで指定していないcategory列を、select句で指定している。

>ORDER BY item_id

item_idは複数の表、インラインビューに存在するので、i.item_id のように、どの表のものか指定する必要があります。

お礼

ご回答頂きまして、ありがとうございます。

$sql="select * from hametome_category c,hametome_item i,(select item_id,category_id,SUM(order_amount) from hametome_order o GROUP BY item_id) AS goukei where i.category_id = $id and c.category_id = i.category_id and o.category_id = i.category_id ORDER BY item_id";

と書いていて、

この内容ですと

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ファイル名～行数

というエラーが出ます。エラー内容からすると直前のSQLクエリに不備があるということだと思うのですが、それが具体的にどの部分だか分からない状態でして…。

商品のカテゴリーのテーブル（hametome_category）、商品のテーブル（hametome_item）、注文のテーブル（hametome_order）から、希望するような表を作るには具体的にどう書けばいいのでしょうか？

補足

自己解決しました。

回答No.1

chukenkenkou

MySQLのバージョンは？
MySQL 4.0までか、MySQL 4.1以降かで、サブクエリの使用可否など実装の仕方に大きな違いが生じます。

>$sql="select *,SUM(order_amount) AS goukei from hametome_order
> GROUP BY item_id";
>とすれば、

group byの使い方を、間違っていませんか？
group byで指定した列、sum等の集計（集合）関数以外の列の値は、MySQLは保証しませんよ？
http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

MySQL 4.1以降なら、

select ・・・
from t1
,(select ・・・ from t2
group by ・・・) as wt2
where 結合条件

や

select ・・・
from t1
inner join -- 必要により、left join　などに変更
(select ・・・ from t2
group by ・・・) as wt2
on 結合条件

といったように、インラインビュー（MySQLでは「from句のサブクエリ」と呼ぶ）で書く方法が簡単です。

お礼

早速のご回答ありがとうございます。

＞MySQLのバージョンは？
失礼いたしました。MySQLのバージョンは4.1.18 となります。

質問の内容を簡潔にしようとしすぎ、分かりにくかったようで申しわけありません。

差し当り、

$sql="select * from hametome_category c,hametome_item i where i.category_id = $id and c.category_id = i.category_id ORDER BY item_id";

上記のsql文で、カテゴリーごとの商品名・単価・在庫数等を抽出して、一覧表を表示させる事は出来ていて、質問内容で言えば、【作りたい表のイメージ図】の【注文数累計】以外は抽出出来ている状態です。

これに注文内容（hametome_orderに登録されているデータの内、注文数）をitem_idでグループ化し合計したものを組み合わせたいと思っているのですが、

$sql="select * from hametome_category c,hametome_item i,(select item_id,category_id,SUM(order_amount) from hametome_order o GROUP BY item_id) AS goukei where i.category_id = $id and c.category_id = i.category_id and o.category_id = i.category_id ORDER BY item_id";

のように書くとどうもうまくいきません。

引き続きご教授頂けますと幸いです。