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ベストアンサー
- 回答日時:
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そのものもそうですが、そこに使われているテクニック、考え方が非常に参考になりました。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・【お題】絵本のタイトル
- ・【大喜利】世界最古のコンビニについて知ってる事を教えてください【投稿~10/10(木)】
- ・メモのコツを教えてください!
- ・CDの保有枚数を教えてください
- ・ホテルを選ぶとき、これだけは譲れない条件TOP3は?
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・【コナン30周年】嘘でしょ!?と思った○○周年を教えて【ハルヒ20周年】
- ・ハマっている「お菓子」を教えて!
- ・最近、いつ泣きましたか?
- ・夏が終わったと感じる瞬間って、どんな時?
- ・10秒目をつむったら…
- ・人生のプチ美学を教えてください!!
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エクセルの関数について教えて...
-
副問合せの書き方について
-
テーブルの結合について
-
クエリ表示と、ADOで抽出したレ...
-
SQLサーバから、項目の属性(型...
-
エラー 1068 (42000): 複数の主...
-
Updateの複数テーブル条件時のL...
-
SQL Left Join で重複を排除す...
-
1テーブル&複数レコードの更新...
-
ストアドのエラーについて
-
時間により単価が変わるログ集...
-
selectした大量データをinsert...
-
LAST_INSERT_IDで同時にアクセ...
-
TreeViewのCheckBoxについて
-
無線LANのチャンネルの固定をし...
-
group byのSQLでインデックスを...
-
Access VBAでのIDの自動発番
-
excel差込印刷用にDBを単票→複...
-
PL/SQLでCREATE SEQUENCEの呼び...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エクセルの関数について教えて...
-
副問合せの書き方について
-
SQLサーバから、項目の属性(型...
-
VIEWの元のテーブルのindexって...
-
マイクラPC版のコマンドで効率...
-
SQL Left Join で重複を排除す...
-
エラー 1068 (42000): 複数の主...
-
select文のwhere句に配列を入れ...
-
バインド変数について
-
SQLにて特定の文字を除いた検索...
-
Access パラメータクエリをcsv...
-
WordpressのContact form 7でzi...
-
PL/SQLの変数について
-
1テーブル&複数レコードの更新...
-
ある条件の最大値+1を初番する...
-
Updateの複数テーブル条件時のL...
-
sqlで、600行あるテーブルを100...
-
”photo id” とは何ぞや?
-
クエリ表示と、ADOで抽出したレ...
おすすめ情報