質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.61%

  • Access

    409questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 181

ma2_ra

score 3

二つのクエリ(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の記述がよくわかりません。

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

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 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は予約語なので項目名等で使用するのお勧めしません。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/14 16:14

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

    キャンセル

  • 2018/06/14 16:19

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

    キャンセル

  • 2018/06/14 16:29

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

    キャンセル

  • 2018/06/14 16:49 編集

    クエリ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)

    キャンセル

  • 2018/06/14 21:38

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

    キャンセル

  • 2018/06/26 11:40

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

    キャンセル

0

まずクエリ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]

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/12 23:25 編集

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • Access

    409questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

  • トップ
  • Accessに関する質問
  • クエリ間で互いの片方にしかないデータも抽出する+特定のフィールドの内容をフィールド名に変え、数を返す