Access SQLで"JOIN 式はサポートされていません"というメッセージが表示されます

Question

グループ階層の情報を持つテーブルとグループ名などの情報を持つテーブルがあります。
やりたい事としては以下の通りです。
１）上記2テーブルを結合させ、親グループID、グループ名を取得
２）１）で取得したグループが持つ子グループID,グループ名を取得

以下のSQLで実行しようとすると、タイトルに記載してある"JOIN 式はサポートされていません"というメッセージが
表示され、実行出来ない状態です。

SELECT N1.parentid AS GroupID,G1.group_name AS GroupName
FROM public_groupandpeople AS N1 INNER JOIN public_groups AS G1 ON N1.parentid = G1.group_id
WHERE (N1.parentid='XXXXX') and Not (N1.parentid)=''
GROUP BY N1.parentid,G1.group_name
UNION
SELECT N2.parentid AS GroupID,G2.group_name AS GroupName
FROM       ((public_groupandpeople AS N1 INNER JOIN public_groups AS G1 ON N1.parentid = G1.group_id) 
LEFT JOIN ((public_groupandpeople AS N2 INNER JOIN public_groups AS G2 ON N2.parentid = G2.group_id)) ON N1.childid = N2.parentid)
WHERE (N1.parentid='GR00002Y') and Not (N2.parentid)=''
GROUP BY N2.parentid,G2.group_name;

どこを直せばいいのか、まったく見当もつかない状態です・・・。
正しいSQLについて教えて頂けますと幸いです。

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

上記SQLはユニオンクエリになっていましたので、エラーが発生している方のクエリのみ切り離しました

SELECT N2.parentid AS GroupID,G2.group_name AS GroupName
FROM       ((public_groupandpeople AS N1 INNER JOIN public_groups AS G1 ON N1.parentid = G1.group_id) 
LEFT JOIN ((public_groupandpeople AS N2 INNER JOIN public_groups AS G2 ON N2.parentid = G2.group_id)) ON N1.childid = N2.parentid)
WHERE (N1.parentid='GR00002Y') and Not (N2.parentid)=''
GROUP BY N2.parentid,G2.group_name;

エラー画像

Answer 1

取りあえず原因の切り分けからしましょう。

２つのSQLを UNION してますが、それぞれのSQLを単独で実行した場合、どちらのSQLでエラーがでますか。

エラーが出たほうのSQLをデザインビューにして、その画像を添付してもらえますか。

コメントを受けて追記

質問者さんの 2017/07/03 15:53コメントから引用

public_groupandpeople
*parentid *childid
----------------------------
GR0000 GR0001
GR0000 GR0002
GR0000 GR0003
GR0001 GR0001X
GR0001 GR0001Y
GR0002 GR0002X
GR1000 GR1001
GR1001 GR1001X
GR1001 GR1001Y

このテーブルは下記のような階層モデルを表していると思います。

GR0000┬GR0001┬GR0001X
　　　│　　　└GR0001Y
　　　├GR0002─GR0002X
　　　└GR0003
GR1000─GR1001┬GR1001X
　　　　　　　└GR1001Y

これから、GR0000配下にあるグループID を取得するには、下記のSQLになります。

SELECT DISTINCT parentid 
FROM public_groupandpeople 
WHERE parentid='GR0000'
UNUON ALL
SELECT childid
FROM public_groupandpeople
WHERE parentid="GR0000"
UNUON ALL
SELECT T1.childid
FROM public_groupandpeople T1 INNER JOIN public_groupandpeople T2
 ON T1.parentid = T2.childid
WHERE T2.parentid="GR0000";

ルート階層のデータ
UNION ALL
１階層下のデータ
UNION ALL
２階層下のデータ;

という構成になっています。

このSQLをクエリとして保存します。名前はQ1とします。

SELECT Q1.parentid, T1.group_name
FROM Q1 INNER JOIN public_groups T1 ON Q1.parentid = T1.group_id;

出力結果

parentid	group_name
---------------------
GR0000	グループA
GR0001	グループB
GR0002	グループC
GR0003	グループD
GR0001X	グループE
GR0001Y	グループF
GR0002X	グループG

GR0000配下のグループデータが出力されます。

Q1をサブクエリにすれば一つのクエリに纏められます。

SQLで階層モデルを表現するのは難しいです。現状のテーブル設計が最適とはいえないかもしれません。下記辺りを参考にして設計を再考するのもいいかもしれません。

階層構造データへの挑戦 - Qiita
Fertile Forest Model （ツリー構造データをRDBで扱うための第五のモデル） - Qiita

Comments

[退会済みユーザー]

失礼いたしました。
後者のSQLの方にエラーが発生しています。前者については問題なく動作しております。

デザインビューのキャプチャを取ろうと思いましたが、エラーメッセージが表示され
デザインビューが開けない状態です。

他に何か必要情報などありましたら教えてください。

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

[hatena19]

結合の仕方かおかしいように思います。
どのような結果を想定しているのか不明なので、現状の各テーブルの主なフィールド名と主キー設定、
データ例とそのデータから欲しい結果の出力例を提示してもらえますか。

[退会済みユーザー]

ご返事が遅くなりまして、大変申し訳ございません・・・・。

簡易ですが、以下、二つのテーブルがあります。
左側のテーブルは親グループID、子グループIDの情報を階層的に持っているテーブルで
主キーはparentid,childidです

右側のテーブルは各グループIDのグループ名情報を持っているテーブルで
主キーはgroup_idです

|parentid|childid | |group_id |group_name|
------------------- ---------------------------
|GR0000|GR0001| |GR0000 |グループA|
|GR0000|GR0002| |GR0001 |グループB|
|GR0000|GR0003| |GR0002 |グループC|
|GR0001|GR0001X| |GR0003 |グループD|
|GR0001|GR0001Y| |GR0001X |グループE|
|GR0002|GR0002X| |GR0001Y |グループF|
|GR0002|GR0002Y| |GR0002X |グループG|
|GR0002|GR0002Z| |GR0002Y |グループH|
　　 |GR0002Z |グループI |

ます、parentidとgroup_idで結合させますが、以下のような事を実現したいと思っています。
１）GR0000というグループIDとグループ名を取得
２）GR0000が持つ子グループIDとそのグループ名を取得
３）２）で取得した子グループが持つ子グループIDとそのグループ名を取得

で、GR0000と同レイヤーのグループもあって、それらのグループは除外したいので
上記SQL文では以下のように指定しています。

WHERE (N1.parentid='GR0000') and Not (N1.parentid)=''

で、UNIONしている後者のSQLについても以下で記載しています。

WHERE (N1.parentid='GR0000') and Not (N2.parentid)=''
※質問の方は"GR00002Y"と記載されていますが、スルーしてください。常に同じ親グループIDを指定しています。

以上、上手く説明が出来ず、大変恐縮ですがご教示頂けますと幸いです。
よろしくお願いいたします。

[hatena19]

ごめんなさい。せっかく細かく書いていただいのですが、肝心なところが抜けているので、把握できません。

まず、「左側のテーブル」「右側のテーブル」というのは、それぞれ public_groupandpeople と public_groups のどちらを指すのですか。

データ例は、public_groupandpeople と public_groups のどちらのテーブルのものですか。
あるいは、２つのテーブルを結合した結果ですか。

下記のような感じで書いてもらうと分かりやすいです。

*が付いたフィールドが主キー

public_groupandpeople
*parentid *childid group_id
----------------------------
GR0000 GR0001 GR0000
GR0000 GR0002 GR0001
GR0000 GR0003 GR0002
GR0001 GR0001X GR0003
GR0001 GR0001Y GR0001X
GR0002 GR0002X GR0001Y
GR0002 GR0002Y GR0002X
GR0002 GR0002Z GR0002Y

public_groups
*group_id group_name
----------------------
GR0000 グループA
GR0001 グループB
GR0002 グループC
GR0003 グループD
GR0001X グループE
GR0001Y グループF
GR0002X グループG
GR0002Y グループH

欲しい結果
GroupID　group_name
--------------------
???? ?????

このデータ例は、あくまで私の勝手な推測ですので、表記方法の参考にとしてください。

しかしながら、親子関係をもつ階層構造データをSQLで扱うのはなかなか難しいようです。
親子関係を表すので、parentid childid group_id と３つフィールドがあるというテーブル設計から
おかしいように感じます。

階層構造データへの挑戦 - Qiita
http://qiita.com/uchinami_shoichi/items/5fa52f340003107d46c1

[退会済みユーザー]

大変、失礼いたしました。
改めて以下、記載いたします。
分かりやすいよう、少々public_groupandpeopleテーブルについて
変えてみました。

public_groupandpeople
*parentid *childid
----------------------------
GR0000 GR0001
GR0000 GR0002
GR0000 GR0003
GR0001 GR0001X
GR0001 GR0001Y
GR0002 GR0002X
GR1000 GR1001
GR1001 GR1001X
GR1001 GR1001Y

public_groups
*group_id group_name
----------------------
GR0000 グループA
GR0001 グループB
GR0002 グループC
GR0003 グループD
GR0001X グループE
GR0001Y グループF
GR0002X グループG
GR1000 グループH
GR1001 グループI
GR1001X グループJ
GR1001Y グループK

欲しい結果
*parentid *group_name
----------------------
GR0000 グループA
GR0001 グループB
GR0001X グループE
GR0001Y グループF
GR0002 グループC
GR0002X グループG
GR0002Y グループH

という感じです。
ここでやりたい事としては、GR0000,G1001はトップレイヤーのグループですが、
GR0000配下にあるグループ情報のみ取得できるようにしたいのです。

イメージが伝わりましたでしょうか・・・・？

[hatena19]

ようやく把握できました。（２つのテーブルを横に並べているとは思いもよらなかった）
回答に追記しましたので参照ください。

[退会済みユーザー]

ご回答ありがとうございます！
正直、まだSQLについて理解できていない部分もあるので少しずつかみ砕きながら
理解していきたいと思います。

こちらで解決できそうだと思います。本当にありがとうございましたm(__)m