複数テーブルからの抽出

質問

メンバーテーブルに紐づいて性格テーブルと趣味テーブルを作りました。

データを抽出したいのですが、うまくできずに悩んでいます。
下記の2パターンを抽出したい場合、それぞれどうなるのでしょうか？
今はわからなくて、何回もSQLを投げています。

(1)趣味CDが1か3、性格CDが2の人だけ抽出したい⇒結果：会員IDが4の人だけ
(2)趣味CDが1か3、かつフリーワードとして「犬」を趣味テキストか性格テキストに含むレコードを抽出したい⇒結果：会員IDが3の人だけ

■テーブル■
◎メンバーテーブル
会員ID (PK)
性別
都道府県
血液型
1 女性 東京都 B型
2 女性 東京都 A型
3 男性 茨城県 B型
4 女性 千葉県 O型
--------------------------------
◎性格テーブル
会員ID (PK)
性格CD (PK)
性格テキスト
1 1
1 2
2 3
3 999 あまり周りが気にならない性格
4 1
4 2
4 999 ほがらか
---------------------------------
◎趣味テーブル
会員ID (PK)
趣味CD (PK)
趣味テキスト
1 2
2 999 犬の散歩
3 999 犬と遊ぶこと
3 2
3 3
4 1
---------------------------------
★性格CD
1:優しい
2:明るい
3:のんびり屋
999:性格テキスト入力
---------------------------------
★趣味CD
1:ドライブ
2:音楽
3:旅行
999:趣味テキスト入力
---------------------------------

回答No.1

yambejp · ベストアンサー

実は問題が山積です

基本的にテーブルの作りがあまりよいとは言えません。
趣味テーブルはIDを紐づけるテーブルにすべきで、
趣味テキストについては別テーブルで管理すべきでしょう。
（性格についてもおなじ）
またMySQLのバージョンによってやり方が違います。
一応サブクエリが使える前提で処理を考えます。
それと命題(2)の戻り値は3ではなく2ですよね？
また日本語を『フリーワードとして「犬」を趣味テキストか性格テキストに含む』
などはSQLではあまり得意な処理ではなくスピードが出せません。
（英語であれば全文検索を使えるのですが・・）

以上を踏まえて、それでもやりたいということであれば以下のとおり

(1)これはサブクエリを使えばそれほど問題ありません。
SELECT M.* FROM メンバーテーブル AS M
INNER JOIN (
SELECT DISTINCT 会員ID FROM 趣味テーブル
WHERE 趣味CD IN (1,3)
) AS H ON M.会員ID=H.会員ID
INNER JOIN (
SELECT DISTINCT 会員ID FROM 性格テーブル
WHERE 性格CD IN (1,3)
) AS C ON M.会員ID=C.会員ID

(2)性格に「犬」というのが微妙ですがこんな感じで？

SELECT M.* FROM メンバーテーブル AS M
INNER JOIN (
SELECT 会員ID
FROM 趣味テーブル
WHERE 趣味テキスト LIKE '%犬%'
UNION SELECT 会員ID
FROM 性格テーブル
WHERE 性格テキスト LIKE '%犬%'
) AS X ON M.会員ID=X.会員ID
INNER JOIN (
SELECT DISTINCT 会員ID FROM 性格テーブル
WHERE 性格CD IN (1,3)
) AS C ON M.会員ID=C.会員ID