AccessのSQL URL|大文字|小文字判別

質問

URLのパターンを見て、URLがマッチしたらカテゴリ情報を出力するクエリをAccessで作っています。

・URL_M（URLのパターンを格納するマスタ）
RULE_ID | URLパターン | カテゴリ | 階層
1 | test/area | cate1 | 詳細
2 | test/A | cate2 | Top

・DATA（いろいろなURLを格納するデータ）
URL_ID | URL
1001 | test/a
1002 | test/area12/index.htm
1003 | test/A/index.htm

・出力したいVIEW
URL_ID | RULE_ID | URL | URLパターン | カテゴリ | 階層
1001 | 空 | test/a | 空 | 空 | 空 |
1002 | 1 |test/area12/index.htm | test/area | cate1 | 詳細 |
1003 | 2 |test/A/index.htm | test/A | cate2 | Top |

これを出力するAccess2013のクエリで作りたいです。

SELECT D.URL_ID, RULE_ID,D.URL, U.URLパターン, U.カテゴリ, U.階層
FROM DATA AS D LEFT JOIN URL_M AS U ON
(
(D.URL Like U.URLパターン+"*")
and
(U.URLパターン = select max( U2.URLパターン) from DATA as D2 LEFT JOIN URL_M as U2 on D2.URL Like U2.URLパターン+"*" )
)

こんな感じで組んでみたのですが、構文エラーが出て、うまくいきません。

また大文字小文字の区別をつける方法も知りたいです。
URL_ID：1001は、RULE_ID：2にマッチせず、空で出力したいです。
バイナリで比較しなければできないということまでは調べたのですが・・・・

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q109179500
http://www.lasical.com/2010/12/27/370/
http://www.f3.dion.ne.jp/~element/msaccess/AcTipsBinaryField.html

SQLがわかる方、教えていただければ幸いです。

よろしくお願い致します。

回答No.2

hatena1989 · ベストアンサー

前回の回答は、部分一致のコードでした。
前方一致なら、

SELECT D.URL_ID, U.RULE_ID, D.URL, U.URLパターン, U.カテゴリ, U.階層
FROM DATA D LEFT JOIN URL_M U ON InStr(1, D.URL, U.URLパターン,0)=1;

補足
もし、

RULE_ID | URLパターン | カテゴリ | 階層
1 | test/area | cate1 | 詳細
2 | test/A | cate2 | Top
3 | test/AB | cate3 | Top

というように途中までは同じURLパターンがあると、結果が重複する場合があります。
その場合は、URLパターンの長い方を優先するという場合は、

SELECT
DATA.URL_ID, Q.RULE_ID, URL, Q.URLパターン, Q.カテゴリ, Q.階層, Q.URL_ID
FROM
DATA LEFT JOIN
(SELECT M.*, Q.URL_ID
FROM URL_M M INNER JOIN
(SELECT URL_ID, Max(T.URLパターン) AS URLパターン
FROM
(SELECT URL_ID, URLパターン
FROM DATA AS D INNER JOIN URL_M AS U
ON InStr(1, D.URL, U.URLパターン,0)>0) AS T
GROUP BY URL_ID) Q
ON M.URLパターン = Q.URLパターン) Q
ON DATA.URL_ID = Q.URL_ID;

お礼

hatena1989さん、回答ありがとうございました！
まさに期待していた値が出ました。

likeでどうにか解決しようと思っていたのですが
InStrという関数があるのですね。
知りませんでした。

部分一致なら、文字が返ってくるので0以上、
前方一致なら、1文字目が一致するから=1
ということですよね。

また、補足でURLの長い方を優先する、
まさに期待通りのお答えを頂いて、感謝です！

こんなに複雑な副問い合わせを使うのですね。

重複レコードが出てしまって、
どうやって１つに絞り込めばよいのかわからなかったので、
maxで最大の長さのものを拾ってこようと思ったのですが
うまくいかず、困りきっておりました。

本当に助かりました。ありがとうございます！！！

正直に申し上げると、上記のSQLを理解しきってはいないのですが
SQLを内側から読み解くと

（１）DATAのURLとURL_Mを部分一致（0以上）でしぼったものを「T」とする
（２）重複を省くために、「T」のURLパターンの長いものをmaxとgroup byで作る（名称なし？）
（３）URL_MST の別名「M」と今作った名称なしのMaxのものをM.URLパターン = Q.URLパターンで一致したものを「Q」とする
（４）DATAに「Q」をleft outer joinでURL_IDでひもづける

ということですよね。

（２）まではなんとなく、わかったのですが（３）と（４）が何をしているのか
勉強不足で理解が及ばず、申し訳ありません。

ANDではなく、（３），（４）のように副問い合わせで入れ子にしないと、
重複レコードの絞込はきっとできないのですよね。

SQLの結果としては、本当に期待通りのものです。

ありがとうございました！

回答No.1

hatena1989

下記でどうですか。

SELECT D.URL_ID, U.RULE_ID, D.URL, U.URLパターン, U.カテゴリ, U.階層
FROM DATA D LEFT JOIN URL_M U ON InStr(1, D.URL, U.URLパターン,0)>0;

お礼

ご回答ありがとうございました！
No2の方にも書きましたが、本当に助かりました！