SQLの書き方を教えてください

Question

t_shop

id	name
1	店舗1
2	店舗2
3	店舗3

t_category

id	name
1	カテゴリー1
2	カテゴリー2
3	カテゴリー3

t_shop_map

id	shop_id	cat_id
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	3	3

上記の３つのテーブルがあり、
・カテゴリーが「カテゴリー2」である店舗の一覧を表示（例として「カテゴリー2」とします）
・各店舗ごとにその店舗が属するカテゴリーも表示する

というのを実現したいのですが、SQLはどのように書けばいいでしょうか？
データを取得して、PHPを用いてHTMLに出力したいです。

SQLを複数回実行するのではなく、クエリ1回実行で、PHPでの処理は簡潔にしたいです。

Comments

[yambejp]

店舗とカテゴリーのデータが逆じゃない？

[avantgarden]

ご指摘ありがとうございます。修正しました。

[kaputaros]

「SQLを複数回実行するのではなく、クエリ1回実行で、PHPでの処理は簡潔にしたいです。」というのは、店舗の切替等の場合もDBアクセスしない、ということですか？

Answer 1

・カテゴリーが「カテゴリー2」である店舗の一覧を表示（例として「カテゴリー2」とします）
・各店舗ごとにその店舗が属するカテゴリーも表示する

こんな感じ

select  shp.name as shop_name, cat.name as category_name
from  t_shop shp 
      left join t_shop_map map
      on    shp.id=map.shop_id
      left join t_category cat
      on    map.cat_id=map.id
where shp.id in (select shp_id from t_shop_map where cat_id=2)

ただ、店舗一覧にカテゴリーを表示するという事なので、店舗は１件で表示するような場合には工夫が必要です。

select  shp.name as shop_name, group_concat(cat.name) as category_name
from  t_shop shp 
      left join t_shop_map map
      on    shp.id=map.shop_id
      left join t_category cat
      on    map.cat_id=map.id
where shp.id in (select shp_id from t_shop_map where cat_id=2)
group by    shp.name

Comments

[avantgarden]

言われてみれば「なるほど」なのですが、SQLに不慣れで辿りづけませんでした。
ありがとうございました。

Answer 2

• 元データ

create table t_shop(id int primary key,name varchar(30));
insert into t_shop values
(1,'店舗1'),
(2,'店舗2'),
(3,'店舗3');

create table t_category(id int primary key,name varchar(30));
insert into t_category values
(1,'カテゴリー1'),
(2,'カテゴリー2'),
(3,'カテゴリー3');

create table t_shop_map(id int primary key,shop_id int,cat_id int,unique(shop_id,cat_id));
insert into t_shop_map values
(1,1,1),
(2,1,2),
(3,1,3),
(4,2,1),
(5,2,2),
(6,3,3);

カテゴリーが「カテゴリー2」である店舗の一覧を表示

select * from t_shop as t2 where exists(
select 1 from t_shop_map as t1 where exists(
select 1 from t_category where name='カテゴリー2' and id=t1.cat_id
) and shop_id=t2.id
);

各店舗ごとにその店舗が属するカテゴリーも表示する

select t1.name,group_concat(t3.name order by t3.id) as categories
from t_shop as t1
inner join t_shop_map as t2 on t1.id=t2.shop_id
inner join t_category as t3 on t2.cat_id=t3.id
group by name;