３つの関連するテーブルから必要な情報を取り出す

質問

現在、データベースで小規模の検索システムを製作しているのですが、詰まってしまったところがあるので、質問をさせて頂きたいです。
まず、最初に取り出したいテーブルの構成を書きたいと思います。
"Tag" "Works" "EquipTag"　の三つのテーブルがあり、

Tag /* Tagをあらわすテーブル*/
id: primaryキー
name: 名前
created_at: 作成日

Works　/* 作品を表すテーブル*/
id: primary キー
name: 名前
created_at: 作成日

Equip_Tag /*作品とタグの関連を保存するテーブル*/
id: primaryキー
works_id: 外部キー Worksのprimaryキーに対して
tag_id: 外部キー Tagのprimaryキーに対して

となっています。
上記のテーブルから、 「タグの名前に '%hoge%'を持ち、かつ、作品の名前に'%hogehoge%'を含む作品一覧」を取得したいのですが、どういった方法が考えられるでしょうか？
また、Worksのidが同じものは２度表示しないようにしたいと考えています。
使用しているＤＢは、MySQL のバージョン５です。
どうか宜しくお願いします。

回答No.3

masa6272 · ベストアンサー

とすると、サブクエリーですね。

SELECT W.id, MAX(W.name), MAX(created_at)
FROM Works AS W,Equip_Tag AS E
WHERE E.works_id = W.id
AND EXISTS (SELECT *
FROM Tags
WHERE E.tag_id = id
AND name LIKE '%hoge%')
AND EXISTS (SELECT *
FROM Tags
WHERE E.tag_id = id
AND name LIKE '%geho%')
AND W.name LIKE '%hogehoge%'
GROUP BY W.id;

動かしてないんで、危ないですが。

お礼

上記で、自分の書いているＳＱＬですが、これでは、すべての行が選ばれてしまいますよね。
とりあえず、以下のようにして、解決しました。
SELECT W.id, MAX( W.name ) , MAX( created_at )
FROM works AS W, equip_tag AS E
WHERE E.works_id = W.id
AND W.id IN(
SELECT Equip_Tag.works_id
FROM Equip_Tag , Tag
WHERE Equip_Tag.tag_id = Tag.id
AND Tag.name LIKE '%geho%'
)
AND W.id IN (

SELECT Equip_Tag.works_id
FROM Equip_Tag , Tag
WHERE Equip_Tag.tag_id = Tag.id
AND Tag.name LIKE '%hoge%'
)
AND W.name LIKE '%hogehoge%'
GROUP BY W.id
とにかく、masa6272さんにはお世話になりました。ありがとうございました

補足

masa6272さん、ご回答ありがとうございます。

回答そのままでは動きませんでしたが、サブクエリーを複数回使うということが今までなかった私にとって、とてもいいヒントになりました。
ありがとうございます。
SELECT W.id, MAX( W.name ) , MAX( created_at )
FROM works AS W, equip_tag AS E
WHERE E.works_id = W.id
AND EXISTS (

SELECT *
FROM Equip_Tag , Tag
WHERE Equip_Tag.tag_id = Tag.id
AND Tag.name LIKE '%geho%'
)
AND EXISTS (

SELECT *
FROM Equip_Tag , Tag
WHERE Equip_Tag.tag_id = Tag.id
AND Tag.name LIKE '%hoge%'
)
AND W.name LIKE '%hogehoge%'
GROUP BY W.id
にて望みの結果が得られました。

masa6272さんの例ですと、ひとつの行となってしまっている E をサブクエリの中で使っているのが問題？なのでしょうか。　動作しなかった原因がそこにありそうなのはわかったのですが、理由がわかりません。
また、masa6272さんはとてもお詳しいですが、ＳＱＬを勉強するのにお勧めの本などありますでしょうか？

回答No.4

masa6272

お勧めはSQLそのもののテキストではなく、SQLで何ができるかの本でしょうね。
「SQLパズル」「プログラマのためのSQL」なんかはどうでしょう?

お礼

「ＳＱＬパズル」　「プログラマのためのＳＱＬ」
ともに有名なようですね。
ＳＱＬは通常のプログラミングとは考え方の違うところがあって（集合演算メインな感じが）　なかなかなじめません。
今日にでも本屋で購入してみようかと思います。
このたびは本当に有難うございました。
また、これ以上は当初の質問とずれてしまうので回答は締め切らせていただきます。

回答No.2

masa6272

試してませんが、素直に

SELECT W.id, MAX(W.name), MAX(created_at)
FROM Tag AS T,Works AS W,Equip_Tag AS E
WHERE E.works_id = W.id
AND E.tag_id = T.id
AND T.name LIKE '%hoge%'
AND W.name LIKE '%hogehoge%'
GROUP BY W.id;

では、ダメですか?
MySQLでは、MAX()を書かなくても動きますが、
形式的に集約してます。

補足

本当にみなさんに申し訳ありませんが、質問が少し間違ってしまっていました。
上記の内容であれば、masa6272さんの回答で問題なく動きます。

問題は、

「TAGの名前が'%hoge%'のTAG, '%geho%'のTAG、両方を持ち,かつ、WORKSの名前に '%hogehoge%'を持つ,WORKSを見つけたい」

ということでした。
折角お答え頂いたのに本当にすみません。

回答No.1

aoi2008

Equip_Tagテーブルって必要なんでしょうか？
TagテーブルにWorks.idへの外部キー付列を作るだけで良いと思いますが・・・。

とりあえずこんな感じですか

SELECT w.id AS works_id, t.id AS tag_id, w.name AS works_name, t.name AS tag_name, w.created_at AS works_created, t.created_at AS tag_created
FROM Works w, Tag t, Equip_Tag e
WHERE w.id=e.works_id AND t.id=e.tag_id AND w.name LIKE '%hoge%' AND t.name LIKE '%hogehoge%'
ORDER BY w.id, t.id;

>>また、Worksのidが同じものは２度表示しないようにしたいと考えています。
この部分については他の人の回答を待つか、プログラム側で制御しても良いと思います。
ちょっと思いつきませんでした

補足

aoi2008さんも、本当にすみません、問題文に抜けがありました。
「TAGの名前が'%hoge%'のTAG, '%geho%'のTAG、両方を持ち,かつ、WORKSの名前に '%hogehoge%'を持つ,WORKSを見つけたい」
というものです。
また、
Equip_Tagをつけている理由は
WorksとTagを多対多の関係にしたいからです。