1対多結合で多を絞り込み条件とするSQLについて

質問

1対多で結合する場合に、多が絞り込み条件となった場合のSQLについての質問です。

たとえばカスタムテーブルを使ったSELECT文などで、このような絞り込み条件が必要になると思います。

まずカスタムテーブルの具体例として、たとえばユーザーテーブルがあったとします。

[user_table]
id=INT //オートインクリメント
user_id=VARCHAR //adminなどのユーザーID文字列
user_pass=VARCHAR //パスワードを保存※ハッシュ化した値
user_name=VARCHAR //山田太郎などのユーザー名
user_mail=VARCHAR //ユーザーのメールアドレス
user_description=TEXT //ユーザーの自己紹介文
user_created=DATETIME //ユーザーの登録日

とりあえず、上記のようなデータをユーザーの基本データだとします。

このユーザーテーブルから、たとえば名前を元に検索するのは単純です。
たとえばこのような感じでしょうか。

SELECT * FROM user_table WHERE user_name = '山田太郎'

このテーブル構造を変更することなくカスタムデータを追加したい(しかも柔軟に)という要望を実現するために、ユーザーカスタムテーブルを作ったとします。

[user_custom_table]
id=INT //オートインクリメント
relational_id=INT //user_table.idへの参照
custom_name=VARCHAR //カスタムフィールド名
custom_value=VARCHAR //カスタムフィールドの値

custom_name、custom_valueには、たとえばそれぞれ以下のような値が入るとします。

custom_name、custom_value
Birthday 、1998/1/1
CompanyName 、○○株式会社
CompanyTel、000-0000-0000
CompanyAddress、東京都千代田区○○-○○

user_custom_table.relational_idはuser_table.idにリレーションしているとすると、INNER JOINして値を取得する方法は判ります。
※カスタムテーブルに値がない場合という状況は無視できる仕様です。

たとえば、会社住所が東京都で始まるユーザのみを抽出すると、以下のような感じでしょうか。

SELECT user_table.* FROM user_table
INNER JOIN user_custom_table
ON user_table.id = user_custom_table.relational_id
WHERE user_custom_table.custom_name = 'CompanyAddress'
AND user_custom_table.custom_value LIKE '東京都%'

ただ、この方法だと、誕生日が○月○日以前で、会社名に○○を含んで、会社住所が東京都で始まり…と検索条件が増えていった場合にINNER JOINがどんどん増えていって、いかにも効率が悪いと思えてなりません。

とりあえず適当に書いてみるとして、以下のような感じでしょうか。

SELECT user_table.* FROM user_table
INNER JOIN user_custom_table AS custom_1
ON user_table.id = custom_1.relational_id
INNER JOIN user_custom_table AS custom_2
ON user_table.id = custom_2.relational_id
INNER JOIN user_custom_table AS custom_3
ON user_table.id = custom_3.relational_id
WHERE custom_1.custom_name = 'CompanyAddress'
AND custom_1.custom_value LIKE '東京都%'
AND custom_2.custom_name = 'Birthday'
AND custom_2.custom_value < '2001/1/1'
AND custom_3.custom_name = 'CompanyName'
AND custom_3.custom_value LIKE '%○○%'

もっと効率の良い書き方、一般的にはこういう場面ではこんな書き方をするなど、識者の方から教えを請いたくて質問しました。

※ちなみにDBはMySQLですが、とくにMySQLに限らない方法で答えを頂ける方がありがたいです。

回答No.1

yambejp · ベストアンサー

user_custom_tableに対して
(relational_id,custom_name)にユニークにするか
(relational_id,custom_name,custom_value)にユニークにするかで若干書き方が違います

仮に、データ管理がしやすい前者だとすると
（所定のユーザーが一つの同名のカスタム名を複数もてないとする）

先にカスタムテーブルで条件を抽出した上でユーザーテーブルの
抽出条件とすればいいでしょう

SELECT user_table.* FROM user_table
where user_table in (select relational_id from user_custom_table
WHERE 0
or (custom_name = 'CompanyAddress' and custom_value LIKE '東京都%')
or (custom_name = 'Birthday' and custom_value < '2001/1/1')
or (custom_name = 'CompanyName' and custom_value LIKE '%○○%')
group by relational_id
having count(*)=3 //custom_nameを連結した数
)

お礼

推測されている通り、当方希望としては前者の
＞(relational_id,custom_name)にユニーク
という条件でした。
説明不足申し訳ありません。

そして素晴らしいお答え、ありがとうございます。
ここまでスッキリしたSQLに書き換えられるとは、想像を遥かに上回った答えでした。

似たようなサブクエリを使う事を考えた事はあったのですが、
＞having count(*)=3 //custom_nameを連結した数
の部分に思い至らなかったために、AND条件ではなくOR条件となってしまうので使えないと考えてしまってました。

countを使って検索条件に一致する数を元に判断する、というテクニックを応用すれば一つのINNER JOINで結合させる事も、EXISTSに書き換える事もできそうですね。
※まあ、EXISTSが早いという伝説は過去の遺物のようですが

うまく応用すれば、あいまい検索のようにcount一致数が近いほど(上記の例の場合にcount=2の場合)似ている検索結果として使えなくもないですね。

SQLそのものもそうですが、そこに使われているテクニック、考え方が非常に参考になりました。
ありがとうございました。