MYSQLを用いた集計について

質問

MySQLを用いた、クロス集計（？）のデータについてどこを探しても見つからなかったので、投稿します。

DBＡとDBＢ、DBＣをLEFT JOINを行って、DBＣの複数条件の全てのに適合する、DBＢの件数をＡのID単位で取得したいと思っています。
＜テーブル構成＞
DB：Ａ
－－－－
id PK
B_id FK
flag
－－－－

DB：Ｂ
－－－－
id PK
C_id FK
flag
－－－－

DB：Ｂ
－－－－
id PK
flag
－－－－

そこで色々試行錯誤してみたのですが、良い方法が見つからず、現在は確認を兼ねて、以下の様な形で抽出するリストを構築する所
までは行いました。
－－－
SELECT *,count(DB_B.id) FROM (DB_A LEFT JOIN DB_B on DB_A.B_id = DB_B.id) JOIN DB_C on DB_B.C_id = DB_C.id WHERE DB_A.flag = 100 AND (DB_C.flag = 1 OR DB_C.flag = 2 OR DB_C.flag = 3) GROUP BY DB_B.id HAVING count(DB_C.id) = 3
－－－
ここで抽出されるリストをDB_Aのid別に件数を取得するにはどうすればよろしいでしょうか？

もちろん、上のコードでは無理だろうと予測はついているのですが、ここから先に進めず…。
ちなみに、環境は以下のとおりです。
MySQL：4.0.27
PHP：4.4.4

※ちなみに、同じような形式でDB＿Ｄの複合条件、DB＿Ｅの複合条件を掛け合わせていく形もあり得るため、それに準じた方法・知恵をご教授いただけると幸いです。

よろしくお願致します。

回答No.2

yambejp

いくつかやり方がありそうですが、
前回書いたとおり集計したものをテンポラリに入れておいて再度
joinしてみました。
（もうすこし効率的なやり方がありそうな気はしますが、とりあえず）

create temporary table temp_shop
select shop.city_id,count(*) as count
from shop
left join
shop_detail as d1 on d1.shop_id=shop.shop_id
and d1.detail_name='海老'
left join
shop_detail as d2 on d2.shop_id=shop.shop_id
and d2.detail_name='鯛'
where 1
and d1.deta_id is not null
and d2.deta_id is not null
group by city_id;
select city.city_id,city.city_name,temp_shop.count
from city
left join temp_shop on temp_shop.city_id=city.city_id

お礼

テンポラリを用いて、答えが出てきました。
頂いた回答だと、テンポラリの中でLEFT JOINを繰り返す為、非常に時間がかかりましたが、それぞれ単一のテンポラリテーブルとして情報を生成し、最後にLEFT JOINでくっつけてそれぞれJOINしたテーブルの固有カラムをis not nullしてやれば、早かったです。

私の解法：
create temporary table temp_flag select *, count(shop_id) as cnt
from shop_detail WHERE detail_name='鯛' OR detail_name='海老' group by shop_id having cnt = 2;
create temporary table temp_shop as sh select *, count(city_id) as cnt_c from shop left join temp_flag as flg sh.shop_id = flg.shop_id WHERE flg.detail_name is not null group by city_id;
select city.city_id,city.city_name,sh.cnt_c from city left join temp_shop as sh sh.city_id = city.city_id WHERE sh.shop_name is not null

補足

テンポラリを使うやり方がいまいちわかってない（と言うより、リファレンスに書かれてありませんでした）ので、探りながらやってみます。
ありがとうございます。

回答No.1

yambejp

もう少しわかりやすい実例をつけてもらうと回答しやすいと思います。

今見た感じでは基本的に1対多のJOINを２回している時点でカウントは
できないと思います。
やるなら最初の集計でテンポラリに落として、それに次のJOINをする
ような流れではないでしょうか

補足

返答ありがとうございます。

お察しの通り、1対NのJOINを複数回繰り返し、その中での集計を行った上でのカウント取得となります。
実例（若干実データに近いのですが）を出すと、この様な形になります。

DB名：city
=====================
city_id | city_name
=====================
1 | 世田谷区
2 | 渋谷区
3 | 新宿区
4 | 太田区
5 | 江戸川区
=====================

DB名：shop
===============================
shop_id | city_id | shop_name
===============================
1 | 1 | 店舗１
2 | 2 | 店舗２
3 | 3 | 店舗３
4 | 2 | 店舗４
5 | 3 | 店舗５
6 | 3 | 店舗６
7 | 4 | 店舗７
8 | 5 | 店舗８
===============================

DB名：shop_detail
===============================
deta_id | shop_id | detail_name
===============================
1 | 1 | 海老
2 | 1 | 鯛
3 | 2 | 海老
4 | 2 | 玉子
5 | 2 | 鯛
6 | 3 | イカ
7 | 3 | 海老
8 | 3 | 玉子
===============================

この中で、海老と鯛どちらもある店舗が、各区で何件あるかを割り出したいと思っています。
今回の場合は世田谷区＝1件、渋谷区＝1件となりますが、この中で「世田谷区」「1件」「渋谷区」「1件」を
出すために、ひとつのSQL文でまとめたいと思っています。

先に提示した確認方法ですと、店舗1と店舗2に該当する2件がある事を確認できるのですが、それを区毎にまとめる事が出来ません。
また、同じ様に、shop_detailと同じような1対Nの検索条件が増えた時に同じ様にまとめていく事が出来ません。
（1対Nのデータのキーカラムはshop_idになります）
それで、解決策を求めて～となります。

一応、質問を出した後で考えた（実験はしてないのですが）方法としては
・1対N毎に検索式を構築し、UNION ALLでまとめる。
・各検索で全件引っかかるデータのみを[city][shop_id]の配列に格納する。
・更にそれをカウントし検索式数分が入っていない場合は、そのshop_idの配列を捨てる
・最後にcityのカウントを取る
でした。

どう見ても力業なので、なるべくなら避けたいと思っています。。。
もっと効率的な方法はないでしょうか。。。