SQLで条件指定結合をしたいがNULLも表示したい

質問

こんにちは、ちょっと複雑なSQLの結合で困っています。

次のような名簿テーブルがあります。

ID, Name, IsMarried
1, 田中, True
2, 鈴木, True
3, 高橋, False
4, 中村, True
「IsMarried」は結婚しているかどうかを示しています。

そして、次のような属性テーブルがあります。

ID, ParentID, Type, Value
1, 1, 1, 55kg
2, 1, 2, 170cm
3, 1, 3, AB型
4, 2, 1, 52kg
5, 2, 2, 165cm
6, 3, 3, B型
7, 4, 1, 45kg
8, 4, 2, 180cm

ParentIDは、名簿テーブルのIDに対応しています。
Typeは、1が体重、2が身長、3が血液型です。
Valueに実際の値が入っています。

ここで、「既婚者」を全員抽出して、"登録されている場合は"その血液型を表示するSQLを書きたいのですが、うまく行きません。どうしたらよいでしょうか？

結果は次のようになります。

ID, Name, BllodType
1, 田中, AB型
2, 鈴木, null
4, 中村, null

Select 名簿.ID, 名簿.Name, 属性.Value from 名簿 [Inner/Left] Join 属性 on 名簿.ID = 属性.ParentID Where 名簿.IsMarried = True AND 属性.Type = 3

このようにすると、血液型が登録されている田中さんしか表示されません。
条件指定を外すと体重や身長も表示されます。

ExcelのVBAからADODBでAccessのmdbファイルにアクセスしています。
Windows 7 64bit、Office 2007です。

回答No.7

kmee

Access持ってないので、確認はできませんが。

#6 の最初の方法だと
名簿.ID,属性.type
4,1
4,2
ができるので、4,NULLの行が無い→where に該当しない、となりそうですが

その次のやつは

Select 名簿.ID, 名簿.Name, 属性.Value from 名簿
Left Outer Join
(select ParentID,Value from 属性 where Type = 3) as T
on 名簿.ID = T.ParentID
Where 名簿.IsMarried = True

で動きませんか?

#1でエラーになる、というのも気になります。

回答No.6

Siegrune

>エラーメッセージは「結合式がサポートされていません」とだけ表示されます。
ということだったのですね。了解です。

Select 名簿.ID, 名簿.Name, 属性.Value from 名簿
Left Outer Join 属性 on 名簿.ID = 属性.ParentID
Where 名簿.IsMarried = True
AND (属性.Type = 3 or 属性.Type is null)

または、

Select 名簿.ID, 名簿.Name, 属性.Value from 名簿
Left Outer Join
(select 属性.Value from 属性 where 属性.Type = 3) as 属性
on 名簿.ID = 属性.ParentID
Where 名簿.IsMarried = True

※ as 属性　は、単に 属性　かも。
あたりではどうでしょう？
（また、サポートされていませんとかでるかもしれませんが。）

お礼

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

AND (属性.Type = 3 or 属性.Type is null)

職人技というか頭が柔らかいというか、感服しました。
Where 句を追加することも試みていたのですが、 3 以外を除外したり、> 3 and < 3 とかで範囲を除外しようとしたり、こざかしいことばかり試していました。AND + () にして、9 と null の両方を取れば条件に一致するのですね。

2つめのSQL文は動きませんでした。
as の部分を変えたり、カッコの中と外でテーブル名に異なる別名を付けましたがうまくどれもfrom文がおかしいとか、結合分がサポートされていないとか表示されてしまいました。

勉強になりました！助かります

回答No.5

山田 太郎（@f_a_007）

補足:JOIN の要件。

Type=3 で　Value=Null の行が存在すること。

ということだと思いますよ。

回答No.4

山田 太郎（@f_a_007）

テーブル構造を変更出来ないのであれば、強制的に列[ BloodType]を生成するしかないと思いますよ。

SELECT 名簿.Name, (Select Value FROM 属性 WHERE 属性.ParentID=名簿.ID AND 属性.Type=3) AS BloodType
FROM 名簿
WHERE (((名簿.IsMarried)=True));

＊添付図ではNull を空で表示しています。

お礼

ありがとうございます。

workaround として、すべての名簿IDに対して、type 3の属性レコードを1件ずつ自動で生成して、血液型はとりあえず "" にするということをしていました。これはこれで動いているのですが、ソフトウェア側から操作した場合に血液型レコードのない人名が登録されてしまうので困っていました。

せっかく教えていただきましたが、シンプルな Where 句で AND (3 or null) のSQLを使うことにします。

お手間を取らせました。
勉強して出直してきますので、またよろしくお願いします。

回答No.3

Siegrune

ANO.1の方のSQLは間違っているように見えないのだけど。
（エラーが出たときは、どういうエラーがでたか詳しく書かないと答えようもないですよ。）

それはさておき、本題のほうですが。

Select 名簿.ID, 名簿.Name, 属性.Value
from 名簿 Left Outer Join 属性
on 名簿.ID = 属性.ParentID
AND 属性.Type = 3
Where 名簿.IsMarried = True
ではどうですか？

Left Join ってものによっては、Left Inner Joinの省略形で、
Inner Koinて書いているのと同じだったりすることもあるので。

お礼

ありがとうございます。

エラーメッセージは「結合式がサポートされていません」とだけ表示されます。
「AND 属性.Type = 3」をそっくり削除すると動作します(期待動作ではありませんが)。
「名簿.ID = 属性.ParentID AND」部分を削除しても同じエラーが出るので、On の後に 属性.Type = 3 という形式は使えないのではないかと疑っています。

エクセルVBAからADODBで操作する場合には使えない書き方なのかもしれませんね。

回答No.2

山田 太郎（@f_a_007）

【属性】

ID___________1
ParentID_____1
Weight_______55Kg
Height_______170cm
BloodType____AB型

Q、どうしたらよいでしょうか？
A、一番良いのは、テーブル「属性」の列を上のように変更。

更に言えば、主テーブルと属性テーブルとに分離する必要性はないようですね。

＜理由＞列[ID]、列[ParentID]の値は常に一致するから。

【名簿】

ID___________1
Name_________田中
IsMarried____True
Weight_______55Kg
Height_______170cm
BloodType____AB型

とテーブル「名簿」の設計を変更すれば、もっと簡単に事は達成できるかと思いますよ。

お礼

ありがとうございます。
残念ながらこの構造は変えられないのです。

あるソフトウェアが扱うデータベースを、エクセルから強制的に変更しようというもので、構造を変えるとソフトウェア側が対応できなくなってしまうのです。

回答No.1

kmee

属性の中から、Type=3のものだけを抜き出して、joinすればよいでしょう。
JOINのONで指定できるのは、tableA.colA=tableB.colB だけではありません。

Select 名簿.ID, 名簿.Name, 属性.Value from 名簿
Left Join 属性 on 名簿.ID = 属性.ParentID AND 属性.Type = 3
Where 名簿.IsMarried = True

お礼

ありがとうございます。

どうもエラーになってしまいます。
この書き方で合ってますでしょうか？