2つのテーブルからのレコード取得

質問

はじめまして。
現在、画面上で検索条件を選択し、検索ボタンを押すと結果を返すアプリケーションを作成しておりますが、
画面上の検索の条件に「空白」を加えると、検索前に空白で表示されていた件数に比べて少ない結果になる、という現象が起こっています。
原因は、テーブルＡとテーブルＢを外部結合させてデータを取得して表示していますが、
検索前はＢにレコードを持たないデータも表示しており、対象項目に検索条件「空白」を設定すると、テーブルＢにレコードを持ち、
かつ該当項目が空白(null)であるデータのみ検索して、テーブルＢにレコードがないデータは検索されないため、検索前より件数が少なく表示されるためだと判明しました。
画面上空白が選択されたら、テーブルＡの該当項目は空白、テーブルＢにはデータを持たないレコードも表示したいのですが、
どのようにすればよいかご教示いただければ有難いです。
（検索条件は複数あり、空白を指定できる項目も複数あるという前提です。）
以上、よろしくお願い致します。

回答No.5

jamshid6 · ベストアンサー

なんとなく誤解があるんだろうなぁと思っていましたが。。

LEFT OUTER JOINの結果のNULLとレコード自体のNULLには何の違いもありません。NULLはNULLです。
以下で確認してください。質問者さんの考え方では、以下でもKEYID=2のレコードが抽出されることに
なりますが、実際には抽出されません。

DECLARE @TBLA TABLE ([KEYID][int],[F1][varchar](5))
DECLARE @TBLB TABLE ([KEYID][int],[F2][varchar](5))
INSERT INTO @TBLA VALUES (1,'X')
INSERT INTO @TBLA VALUES (2,'Y')
INSERT INTO @TBLA VALUES (3,'Z')
INSERT INTO @TBLB VALUES (1,'ABC')
INSERT INTO @TBLB VALUES (3,'BCD')

SELECT a.KEYID,a.F1,b.F2
FROM @TBLA a
LEFT OUTER JOIN @TBLB b ON b.KEYID=a.KEYID
WHERE b.F2='ABC'

お礼

回答が遅くなり、申し訳ありません。
実際に試したところ、LEFT JOINのWHERE句の検索内容に沿ったデータのみ抽出されました。

私自身の混乱の原因としては
(1)「*=」を使用していた際、where句の条件に「項目=Null」を入れていたところ全件ヒットしていた
(2)(1)により、外部結合では、結合される側をwhere句の条件で指定しても全件ヒットしてしまうものと思ってしまった

というところにあるようです。
(1)に関してはSQLserverのバージョンの問題で、曖昧な条件になってしまっているところに原因があるようですが、(2)に関しては外部結合が根本的に分かっていない（結合した後に抽出していることが理解できていない）ところに原因があるように思います。SQLをもっと勉強する必要がありますね。
この度はありがとうございました。

回答No.4

jamshid6

#2,#3です。
>ご提示いただいたＳＱＬですと、検索の際、Ｂのnull以外の検索値を指定した場合、Ｂのテーブルに値を持っていないデータもヒットしてしまうかと思います。
そんなことはないですよ。

例えば、BのF4というカラムに"ABC"という条件を指定すると、条件にはb.F4='ABC'が追加されます。
ということはBに値のない(NULLの)レコードはヒットしないです。

一方、BのF4というカラムに""(空白)という条件を指定すると、条件には(b.F4='' OR b.F4 IS NULL)が追加されます。
したがって、この場合はBに値のない(NULLの)レコードもヒットします。

まあ、あくまで代案として載せたサンプルコードなので、理解できてもできなくても別にかまわないですが。。。

補足

分かりづらい表現ですみません。
「Ｂのテーブルに値を持っていないデータ」というのは、
テーブルＡにはデータがあるが、Ｂには対応するデータがないレコード、という意味です。
LEFT OUTER JOIN の構造でBのF4というカラムに"ABC"という条件を指定すると、
　・BにAと対応するデータを持っていて、かつb.F4='ABCであるレコード
　・BにAと対応するデータを持っていないレコード
がヒットしてしまうのではないかと思います。

回答No.3

jamshid6

#2です。

結局検索条件に合わせてASPかVBScriptでSELECT文を組み上げているわけですよね？
書かれた案でもいいのでしょうけど、素朴な疑問として、AのフィールドとBのフィールドで検索条件の追加方法を変えるだけではだめなんでしょうか。
ざっくりこんな感じで。。
Dim sSQL,sWhere
sSQL = "SELECT a.KEY,a.F1,a.F2,a.F3,b.F4,b.F5,b.F6" & _
　　　　" FROM TABLEA a" & _
　　　　" LEFT OUTER JOIN TABLEB b ON b.KEY=a.KEY"
If bUseF1=True Then sWhere=sWhere & " AND a.F1='" & sF1 & "'"
If bUseF2=True Then sWhere=sWhere & " AND a.F2='" & sF2 & "'"
If bUseF3=True Then sWhere=sWhere & " AND a.F3='" & sF3 & "'"
If bUseF4=True AND sF4="" Then sWhere=sWhere & " AND (b.F4='' OR b.F4 IS NULL)"
If bUseF4=True AND sF4<>"" Then sWhere=sWhere & " AND b.F4='" & sF4 & "'"
If bUseF5=True AND sF5="" Then sWhere=sWhere & " AND (b.F5='' OR b.F5 IS NULL)"
If bUseF5=True AND sF5<>"" Then sWhere=sWhere & " AND b.F5='" & sF5 & "'"
If bUseF6=True AND sF6="" Then sWhere=sWhere & " AND (b.F6='' OR b.F6 IS NULL)"
If bUseF6=True AND sF6<>"" Then sWhere=sWhere & " AND b.F6='" & sF6 & "'"
If LEFT(sWhere,4)=" AND" Then sWhere = " WHERE" & RIGHT(sWhere,LEN(sWhere)-4)
sSQL=sSQL & sWhere

あとは、外部結合の書式ですが「*=」はSQL Server 2005からはサポートされませんから、「LEFT OUTER JOIN」に慣れておいた方がいいですよ。

補足

ご返答ありがとうございます。
ご提示いただいたＳＱＬですと、検索の際、Ｂのnull以外の検索値を指定した場合、Ｂのテーブルに値を持っていないデータもヒットしてしまうかと思います。
（Ｂの検索値に空白以外の値を入力した場合、検索結果はＢにデータも持たないレコードを含まず、Ｂに検索値を持っているデータのみ表示させたい）
なお、空白が指定できる項目は、Ｂにある項目のみになります。

外部結合に関しては、ご教示いただきましたように、今回の修正でLEFT OUT JOINに変更する予定でおります。

回答No.2

jamshid6

画面がVBなのか、Accessなのか、それ以外なのか。検索条件入力後のクエリはどのように構築しているか。
・・が分からないので、純粋にSQL Serverに関する質問と考えて書きますが、

空白を指定できる検索条件が複数あったとしても、「空白またはNULL」の両方を満たすように条件を指定してあげるしかないと思います。

SELECT * FROM TABLEA a
LEFT OUTER JOIN TABLEB b ON b.KEY=a.KEY
WHERE a.FIELD1=@PARM1
AND ISNULL(a.FIELD2,'')=@PARM2
AND ISNULL(b.FIELD1,'')=@PARM3
AND ISNULL(b.FIELD2,'')=@PARM4 ...

テーブルAのうち、NULLの入らない項目についてはISNULLはかぶせないようにし、条件はできるだけ最初の方に持ってくるのがいいでしょう。

検索条件をできる限り触りたくない場合、結合しているテーブルの方をビューにして、ビューの中でテーブルＢ側の
項目に全部ISNULLをかぶせてしまう手もあるかと思います。

CREATE VIEW VIEWA
AS
SELECT
a.KEY1 KEY,
a.FIELD1 AFIELD1,
ISNULL(a.FIELD2,'') AFIELD2,
........
ISNULL(b.FIELD1,'') BFIELD1,
ISNULL(b.FIELD2,'') BFIELD2
FROM TABLEA a
LEFT OUTER JOIN TABLEB b ON b.KEY=a.KEY

で、検索するときには

SELECT * FROM VIEWA
WHERE AFIELD1=@PARM1
AND AFIELD2=@PARM2
AND BFIELD1=@PARM3
AND BFIELD2=@PARM4 ...

補足

状況に関する記述が不足していまして、申し訳ありません。

環境
開発言語：ASP　ＤＢ:SQLserver
問題の箇所は検索ボタンを押してからの動作で、SQLを投げているのはVBScriptです。
Bの項目に含まれる検索項目に入力があるとフラグをオンにし、
フラグがオンの場合：AND A.KEY = B.KEY
フラグがオンでない場合:AND A.KEY *= B.KEY
という条件にしています。
検索条件にnullの入らない項目はＳＱＬ上前に集結させております。

現状、いただいた回答により以下のような案を考えました。
(1)Aにデータを持ち、Bにデータを持たないレコードをserectしたビュー（Vα）を事前に作成しておく
(2)検索時にnullが入る項目にすべてnullを指定された場合、
　AとBにデータを持ち、かつ検索項目を満たすデータ（=内部結合で検索条件に一致するデータ）と、
　Vαで検索条件に一致するデータをunionさせる
　nullが入る項目に1つでもnull以外を指定された場合、Bにデータがあることが前提になるため、
　Vαはunionさせない

上記の方法に矛盾がある、またはほかに方法がある場合、
ご教示いただければありがたいです。

回答No.1

rukuku

こんばんは

データベースについては人様にアドバイスできるほどの経験はなく、自分自身が勉強しながら使ってる者です。

一番に思いつくのは外部結合のRIGHTとLEFTを間違っていませんか？ということです。

テーブルA、テーブルBのフィールドと問題のあるSQL文を提示してもらえれば、もう少しアドバイスできるかもしれません。