SQLの条件検索について

質問

下記のような「果物TBL」というテーブルがあるとします。

果物TBL
--------------------------------
ID　　　　名前　　　　　　値段
- - - - - - - - - - - - - - - - - - - -
01　　　　りんご　　　　　150
02　　　　バナナ　　　　　250
03　　　　みかん　　　　　250
04　　　　ぶどう　　　　　300
--------------------------------

また上記のテーブルと全く同じ構成の「果物検索TBL」というテーブルがあるとします。

そのテーブルには、例として下記のような値を入れておきます。

果物検索TBL
--------------------------------
ID　　　　名前　　　　　　値段
- - - - - - - - - - - - - - - - - - - -
NULL　　　りんご　　　　　250
NULL　　　バナナ　　　　　NULL
--------------------------------

この時、下記のようなSQLだと

SELECT DISTINCT 果物TBL.名前
FROM 果物TBL INNER JOIN 果物検索TBL ON
(果物TBL.ID = 果物検索TBL.ID AND LEN(果物検索TBL.ID) > 0) OR
(果物TBL.名前 = 果物検索TBL.名前 AND LEN(果物検索TBL.名前) > 0) OR
(果物TBL.値段 = 果物検索TBL.値段 AND LEN(果物検索TBL.値段) > 0);

--------------------------------
名前
- - - - - - - - - - - - - - - - - - - -
バナナ
みかん
りんご
--------------------------------

という結果になります。(名前：りんご・バナナと値段：250のOR検索)

その結果を

--------------------------------
名前
- - - - - - - - - - - - - - - - - - - -
バナナ
--------------------------------

という結果にするにはどうすればいいでしょうか？(名前：りんご・バナナと値段：250のAND検索、バナナのみマッチ)
単純にORをANDにしてもうまくいきませんでした。
動的にSQLを作れれば良いのですが、この構成は変えられないのでNULL値がネックになり分かりません。
教えてください。
また、記載してるSQLよりもっとスマートな書き方があれば教えてください。

回答No.7

hatena1989 · ベストアンサー

なんどもすみません（汗）。

ANo.6 ですが、FROM を FORM とタイプミスしています。

SELECT DISTINCT 果物TBL.*
FROM 果物TBL
WHERE
(ID In (SELECT ID FROM 果物検索TBL) AND EXISTS (SELECT 1 FROM 果物検索TBL WHERE ID Is Not Null))
AND
(名前 In (SELECT 名前 FROM 果物検索TBL) AND EXISTS (SELECT 1 FROM 果物検索TBL WHERE 名前 Is Not Null))
AND
(値段 In (SELECT 値段 FROM 果物検索TBL) AND EXISTS (SELECT 1 FROM 果物検索TBL WHERE 値段 Is Not Null));

お礼

何度もありがとうございます。

なんとなくIIFが分かりました。
こっちの書き方のほうがわかりやすいですが、見た目はIIFを使ったほうがスマートですね。
(実行速度に差はあるのかな？)

どうもありがとうございました。

回答No.6

hatena1989

ANo.5 もたぶん間違ってます。

ANo.4 さんの回答が正しいと思います。

考え方は同じですが、別の書き方をすると、

SELECT DISTINCT 果物TBL.*
FROM 果物TBL
WHERE
(ID In (SELECT ID FORM 果物検索TBL) AND EXISTS (SELECT 1 FORM 果物検索TBL WHERE ID Is Not Null))
AND
(名前 In (SELECT 名前 FORM 果物検索TBL) AND EXISTS (SELECT 1 FORM 果物検索TBL WHERE 名前 Is Not Null))
AND
(値段 In (SELECT 値段 FORM 果物検索TBL) AND EXISTS (SELECT 1 FORM 果物検索TBL WHERE 値段 Is Not Null));

回答No.5

hatena1989

＃3ですが勘違いしてました。無視してください。

（ID すべて）かつ（名前が りんご、または、バナナ）かつ（値段 が 250円）というような条件にしたいということなら、
下記でいいかな。

SELECT DISTINCT 果物TBL.*
FROM 果物TBL, 果物検索TBL
WHERE
(果物TBL.ID = 果物検索TBL.ID OR 果物検索TBL.ID Is Null)
AND
(果物TBL.名前 = 果物検索TBL.名前 OR 果物検索TBL.名前 Is Null)
AND
(果物TBL.値段 = 果物検索TBL.値段 OR 果物検索TBL.値段 Is Null);

お礼

偶然、No.3にお礼を書いている時にご回答して下さったようですね^^
再度ご回答ありがとうございます。

果物TBLでは望みどおりの結果が出ました。
今度は実際のテーブルに置き換えて試してみます。

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

回答No.4

30246kiku

ACCESS で確認したものになりますが、

★★　が　果物TBL、　■■　が　果物検索TBL　とします。
■■　には NULL ではないものが必ず１つはある、という条件にて

OR検索

SELECT DISTINCT 名前 FROM ★★ WHERE
IIF(EXISTS (SELECT 1 FROM ■■ WHERE ID IS NOT NULL),
ID IN (SELECT ID FROM ■■), False)
OR
IIF(EXISTS (SELECT 1 FROM ■■ WHERE 名前 IS NOT NULL),
名前 IN (SELECT 名前 FROM ■■), False)
OR
IIF(EXISTS (SELECT 1 FROM ■■ WHERE 値段 IS NOT NULL),
値段 IN (SELECT 値段 FROM ■■), False);

また OR なら以下でも良いですね：上記は AND と書き方合わせるという事で

SELECT DISTINCT 名前 FROM ★★ WHERE
ID IN (SELECT ID FROM ■■)
OR
名前 IN (SELECT 名前 FROM ■■)
OR
値段 IN (SELECT 値段 FROM ■■);

なお、提示された SQL で、AND LEN(・・・)>0 の部分は、いらないような気がします。
（ NULL とは結合できなかったと思うので）

AND検索

SELECT DISTINCT 名前 FROM ★★ WHERE
IIF(EXISTS (SELECT 1 FROM ■■ WHERE ID IS NOT NULL),
ID IN (SELECT ID FROM ■■), True)
AND
IIF(EXISTS (SELECT 1 FROM ■■ WHERE 名前 IS NOT NULL),
名前 IN (SELECT 名前 FROM ■■), True)
AND
IIF(EXISTS (SELECT 1 FROM ■■ WHERE 値段 IS NOT NULL),
値段 IN (SELECT 値段 FROM ■■), True);

でどうでしょう。
処理性能についてはわかりません。

お礼

ご提示頂いたSQLで望みどおりの結果が出ました。
IIFというのがイマイチなんなのかわからないので、SQLの中身はまだ理解できていませんが・・・
調べてみます。

ただNo.1のお礼に書いたのと同様に、実際に使用するテーブルにて実行すると式が複雑と怒られました。
やはり、条件を減らすしか無いのかもしれません。

どうもありがとうございました。

回答No.3

hatena1989

データベースはAccessでしょうか。

Null同士も一致と見なしたいのだと解釈しましたがどうでしょうか。。
Nullは = で比較できないので、変換して比較します。
AccessならNz関数で変換できます。

SELECT DISTINCT 果物TBL.名前
FROM 果物TBL INNER JOIN 果物検索TBL ON
(Nz(果物TBL.ID,"") = Nz(果物検索TBL.ID,"")) AND
(Nz(果物TBL.名前,"") = Nz(果物検索TBL.名前,"")) AND
(Nz(果物TBL.値段,"") = Nz(果物検索TBL.値段,""));

お礼

Accessを使用しております。

ご提示頂いたSQLではうまくいきませんでした。
ただNzという関数は知らなかったので、勉強になります。

どうもありがとうございました。

回答No.2

o_chi_chi

下記でしょうか？

ＩＤはユニークのはずなのでＩＤの一致で抽出

名前＋値段でたぶんユニークになるのでそれぞれの一致で抽出

---
SELECT DISTINCT 果物TBL.名前
FROM 果物TBL
INNER JOIN 果物検索TBL
ON
(果物TBL.ID = 果物検索TBL.ID) OR
(果物TBL.名前 = 果物検索TBL.名前 AND
果物TBL.値段 = 果物検索TBL.値段);

お礼

ご提示いただいたSQLですと、結果が0件になってしまいます(泣)

どっちにしてもNo.1のお礼欄に書いた通り実際にはカラムが多いので、
この書き方ですととても複雑になってしまいそうです。
(最初に一言言っておけばよかったですね。失礼しました。)

ご回答いただきどうもありがとうございました。

回答No.1

jacob-wk9

なかなかクレバーなSQLで、実用以上に複雑な構文になっているかもしれませんね。
一つ目は、こんな式が思いつきました。
SELECT distinct 果物TBL.名前
FROM 果物TBL WHERE ([果物TBL].[名前]) in (SELECT 名前 FROM 果物検索TBL)
OR ([果物TBL].[値段]) in (SELECT 値段 FROM 果物検索TBL)

次の検索条件は、りんごとバナナを最初にセレクトして、かつ２５０円ということですが、
検索TBLで「りんご　　　　　250」となっているのが、非常に扱いづらいというか、これは２５０円のりんごはリストにありますか
という解釈になってしまいます。答えはなしです。
たぶん、検索データが入ったテーブル構造を練り直す必要があると思います。
あえて、検索TBLのお互いのフィールドはまったく無関係である、としゃにむに解釈しますと

SELECT distinct 果物TBL.名前
FROM 果物TBL WHERE ([果物TBL].[名前]) in (SELECT 名前 FROM 果物検索TBL)
AND ([果物TBL].[値段]) in (SELECT 値段 FROM 果物検索TBL)

OR　を　ANDに変えるだけになります。
解釈合ってますでしょうか。

お礼

記述頂いた内容にて望み通りの結果が出ました。
ただ、今回のテーブルは質問用にカラムを減らしましたが、
実際のテーブルはカラムが多く、このSQLを実行しましたところ、
式が複雑すぎますと怒られてしまいました。

条件を減らす等で対処しようかと思います。

どうもありがとうございました。