親と子供が複数のSQL取得方法

質問

親に対して複数の子供がいるようなデータ構造の場合、一つのＳＱＬで取得する方法がわかりません。

例：楽天のようなモールサイトでの商品検索

テーブルは【店舗】と【商品】と【店舗支払方法】

■キー
【店舗】.店舗番号
１つ
【商品】.店舗番号、商品番号
２つ
【店舗支払方法】.店舗番号、支払方法コード（
２つ

店舗は複数(n)

店舗に対して、商品は複数(n)

店舗に対して、支払方法は複数(n)

取得したい結果としては、各店舗の商品一覧です。各商品には店舗支払い方法（代引き、クレジット）がどのようなのがあるかです。

出力結果例としては以下のように表示したいです。

１：店舗Ａ、商品１、代引き、クレジット、銀行振込み
２：店舗Ａ、商品２、代引き、クレジット、銀行振込み
３：店舗Ｂ、商品１、代引き、クレジット
４：店舗Ｂ、商品２、代引き、クレジット
５：店舗Ｃ、商品１、クレジット
６：店舗Ｃ、商品２、クレジット

支払い方法がないのであれば、【店舗】.店舗番号 = 【商品】.店舗番号
というような結合条件でいいのでしょうが、支払い方法が入ってくると別になってきます。

何も考えずにやるのであれば、、【店舗】.店舗番号 = 【商品】.店舗番号
上記の結合で、商品の一覧を取得し、取得した数だけ【店舗支払方法】のテーブルと結合したＳＱＬを実行して、レコードに対して情報を付加してあげれば出来るとは思いますが・・・。

１回で取得する方法はありますか？

環境はMYSQL5.0とＰＨＰ５です。

そもそもテーブルがうまく正規化出来ていないなのがあれば、教えてください。
以上、よろしくお願い致します。

回答No.2

root139 · ベストアンサー

パッと思いつくのは下記の2つですかね。

1. 全部のテーブルを単純に店舗番号で結合し、ホスト言語(PHP)側で連想配列などを使用して、同一店舗・同一商品ごとのデータにまとめる。

例) ----------------------------------------------
SELECT tp.店舗番号, sh.商品番号, sr.支払方法コード
FROM
店舗 tp
JOIN 商品 sh ON (tp.店舗番号 = sh.店舗番号)
LEFT OUTER JOIN 店舗支払方法 sr ON (tp.店舗番号 = sr.店舗番号)
ORDER BY 店舗番号, 商品番号, 支払方法コード;
--------------------------------------------------

2. 支払方法を GROUP_CONCAT を使って連結して取り出し、ホスト言語(PHP)側でその文字列を分割する。

例) ----------------------------------------------
SELECT
tp.店舗番号, sh.商品番号, sm.支払方法
FROM
店舗 tp
join 商品 sh on (tp.店舗番号 = sh.店舗番号)
join
(SELECT 店舗番号, GROUP_CONCAT(sr.支払方法コード) AS 支払方法
FROM 店舗支払方法 sr GROUP BY 店舗番号) sm
ON (tp.店舗番号 = sm.店舗番号)
ORDER BY 店舗番号, 商品番号;
--------------------------------------------------

ちなみに GROUP_CONCAT の区切り文字は SEPARATOR オプションを使って指定できます。
GROUP_CONCAT(sr.支払方法コード SEPARATOR "\t") -- タブ区切りの場合

お礼

ありがとうございます。
抽出することが出来ました！
支払い方法はプログラムの方で分割してみます。

助かりました！

回答No.3

yambejp

こんな構成でどうでしょう？

データ作成：
create table shop(id int not null primary key ,shop_name varchar(30));
insert into shop values(1,'店舗Ａ'),(2,'店舗Ｂ'),(3,'店舗Ｃ');
create table item(id int not null primary key ,item_name varchar(30));
insert into item values(1,'商品１'),(2,'商品２');
create table payment(id int not null primary key,pay_name varchar(30));
insert into payment values(1,'代引き'),(2,'クレジット'),(3,'銀行振込み');
create table shop_item_payment(id int not null auto_increment primary key,shop_id int not null,item_id int not null,payment_id int not null
, unique (shop_id,item_id,payment_id));
insert into shop_item_payment(shop_id,item_id,payment_id)
values(1,1,1),(1,1,2),(1,1,3),(1,2,1),(1,2,2),(1,2,3),(2,1,1),(2,1,2),(2,2,1),(2,2,2),(3,1,2),(3,2,2);

抽出：
select shop_name,item_name,group_concat(pay_name order by p.id)
from shop_item_payment
inner join shop as s on s.id=shop_id
inner join item as i on i.id=item_id
inner join payment as p on p.id=payment_id
group by shop_id,item_id;

お礼

データまで用意して頂いてありがとうございます。
商品テーブルのキーが一つ足りないのでちょっとテーブル構成が違うのですが、このような作り方もあるのだと勉強になりました。
ありがとうございます。

回答No.1

yorozu_ya

店舗番号を渡すと支払い方法の一覧を返すストアドファンクションを作成する。