クエリ間で互いの片方にしかないデータも抽出する＋特定のフィールドの内容をフィールド名に変え、数を返す

Question

二つのクエリ（1,2）があり、ここから得たい結果がクエリ3です。
ポイントは二つあります。

1）クエリ1とクエリ2に共通のフィールドは「name」ですが、
これによって結合させつつ、一致しないデータ（C,D,E）も抽出したい。
※nameの種類は固定ではありません
2）クエリにある「place」の内容はa,bの2種類だけに抽出してあり、
これをplace_a、place_bというフィールドにし、1または0の数で得たい

クエリ1
name|cost
A|2000
B|3000
C|5000

クエリ2
name|place|number
A|a|001
B|a|002
B|b|002
D|b|003
E|a|004

クエリ3
name|cost|place_a|place_b|number
A|2000|1|0|001
B|3000|1|1|002
C|5000|0|0|-
D|-|0|1|0|003
E|-|1|0|004

実はこのあと、最終的にはnameとnumberでグループ化し、
numberで他のクエリと結合する必要があります。

互いに片方にしかないデータも抽出、となるとユニオンクエリでつないで
グループ化してカウント？合計？かな、とは思っているのですが、
SQLの記述がよくわかりません。

初心者でもわかりやすいシンプルな方法をお授けください。

Answer 1

見辛いので「表の挿入」で整形
#クエリ1

name	cost
A	2000
B	3000
C	5000

#クエリ2

name	place	number
A	a	001
B	a	002
B	b	002
D	b	003
E	a	004

#クエリ3

name	cost	place_a	place_b	number
A	2000	1	0	001
B	3000	1	1	002
C	5000	0	0	-
D	-	0	1	003
E	-	1	0	004

本題

片側にしかないものも全て結合する事を**「全部外部結合」(full outer join)と言います。
accessではサポートされていません。
サポートされていない場合にどうするかと言えば、unionしたものを基準にそれぞれ「外部結合」(outer join)**します。

先ずはunionクエリーを作成します。
[クエリ0]

SELECT name FROM クエリ1
UNION select name from クエリ2
;

次に[クエリ2]を元にplace_a,place_bを生成するクエリーを作り保存します。
[クエリ2_1]

SELECT name
     , Max(IIf([place]='a',1,0)) AS place_a
     , Max(IIf([place]='b',1,0)) AS place_b
     , number
FROM クエリ2
GROUP BY name, number
;

クエリ0を元にクエリ1とクエリ2_1を外部結合して、整形します。
[クエリ3]

SELECT クエリ0.name
     , クエリ1.cost
     , クエリ2_1.place_a
     , クエリ2_1.place_b
     , クエリ2_1.number
FROM (クエリ0 LEFT JOIN クエリ1 
     ON クエリ0.name = クエリ1.name
     ) LEFT JOIN クエリ2_1 
     ON クエリ0.name = クエリ2_1.name
;

※nameは予約語なので項目名等で使用するのお勧めしません。

Comments

[ma2_ra]

ありがとうございます。
質問に用いたのはサンプルで、フィールド名はダミー、実際は予約語とかぶりません。
ただ、例示したものを同じクエリを用意し、ご教示いただいたSQLを記述してみたのですが、
クエリ3を実行しようとすると、「メモ型またはOLEオブジェクト型（クエリ0.[name]=クエリ1.[name])のフィールドを結合することはできません。」と出てしまいます・・・。

[sazi]

元々のクエリ1とクエリ2のnameフィールドの型は何ですか？

[ma2_ra]

クエリのnemeフィールドの型って、プロパティの書式ですか？文字書式ですか？
書式は空白で何も選べません。文字書式は「テキスト形式」にしました。
クエリのもとになっているテーブルはいずれも長テキスト型です。

[sazi]

クエリ2_1が以下に該当するようです。元のテーブルの型を短いテキストに変更可能ですか？

[Microsoft Access のクエリでは、メモ型フィールドで GROUP BY 句を使用するとは、制御文字として [メモ型フィールドの内容が表示されます。](https://support.microsoft.com/ja-jp/help/889490/contents-of-the-memo-field-may-appear-as-control-characters-when-you-u)

[ma2_ra]

短いテキストでうまくできました。
実際のデータも短テキストでおさまりそうなので試してみます。

[ma2_ra]

実際にはうまくいかない点が一つあったのですが、これは別課題にしたいと思います。
本課題は解決しました！ありがとうございます。

Answer 2

まずクエリBのplaceを横持ちに変換するのはこう書きます。

select
  [name],
  count(case place when 'a' then 1 end) as place_a,
  count(case place when 'b' then 1 end) as place_b,
  [number]
from
  b
group by [name], [number]

これをクエリAと外部結合するのでこうなります。

select
  a.[name], a.cost, b_ex.place_a, b_ex.place_b, b_ex.[number]
from
  a
  full outer join (
    select
      [name],
      count(case place when 'a' then 1 end) as place_a,
      count(case place when 'b' then 1 end) as place_b,
      [number]
    from
      b
    group by [name], [number]
  ) b_ex on a.[name] = b_ex.[name]

Comments

[sazi]

full joinが使えるようになったのかと思ったけど、よく見るとcaseとかも使われているので、違いますね。
accessですよ。