データの結合が上手く行かず、期待通りレコードが引けません。

Question

会社が利用する、顧客管理の仕組みを作っています。
見積書と請求書作成の機能も付いています。

見積書と請求書を顧客情報を結合して、1レコード毎に纏めて情報を出したいと思っています。

• 見積書・請求書に顧客が紐づいていなくても出力したいです
• 見積書・請求書、どちらか一方のデータがあれば出力したいです

各テーブルの関係

• 見積書と請求書は紐づいているもの、紐づいていないものがあります
• 見積書と請求書は、顧客データと紐づいているもの、紐づいていないものがあります
• 見積書と請求書すべてに、システムを利用している会社（host）のIDが振られています
• 見積書の請求書IDには、値が入っていません

テーブル構成は下記です。

• estimate : 見積書テーブル
• invoice : 請求書テーブル
• host : システム利用会社テーブル
• customer : 顧客テーブル

unionを使って、理想に近い形でデータが取得できましたが、
見積IDの重複が少し出てしまい、上手く行きません。
group byすると、nullもgroup byされるので、レコード数が期待よりだいぶ少なくなります。

select *
from
    (
        select estimate.estimate_id,
               estimate.invoice_id,
               customer.customer_id
        from estimate
                 left outer join
             customer on estimate.customer_id = customer.customer_id
        where
                estimate.host_id = 1203507743
          and
            estimate.deleted_at is null
        union
        select invoice.estimate_id,
               invoice.invoice_id,
               customer.customer_id
        from invoice
                 left outer join
             customer on invoice.customer_id = customer.customer_id
        where
                invoice.host_id = 1203507743
          and
            invoice.deleted_at is null
    ) as q1;

上記実行結果で見積もりIDの重複が無くなれば期待通りになります。

文字数制限のためテーブル生成のSQLとInsert文はgistにあげました。

すみませんが、どなたか分かる方いたら、教えていただけないでしょうか…。

=== gentaroさん指摘=============================================

「1レコード毎に纏めて」の意味を定義してください。

見積書・請求書を印刷できるWebページを作っています。
見積書・請求書はどちらかが削除されてても1行に全て出力します。

と。書いていて普通にJOINして、削除フラグをプログラムから見て
ボタンを出力する・しないすればいいだけだということに気が付きました。

select estimate.estimate_id,
       invoice.invoice_id,
       estimate.deleted_at as a,
       invoice.deleted_at as b
from estimate
left join invoice on estimate.estimate_id = invoice.estimate_id 
where estimate.host_id = 1203507743;

===============================================================

=== Orlofskyさん指摘=============================================

リンク先のINSERT文も各テーブル5件程度に削っては？そうすればここの質問にまとめられるのでは？

重複出る理由が分からず、何削っていいか分からないので、
今回はギブします。ごめんなさい。

少なくした結果を、codeで出すと分かりやすいですよというアドバイスだと思ってます。
どうもありがとうござました。

===============================================================

Comments

[Orlofsky]

希望するSELECT結果も https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記してください。
リンク先のINSERT文も各テーブル5件程度に削っては？そうすればここの質問にまとめられるのでは？

[gentaro]

ひとつの見積書IDに対し、見積書→請求書への参照が不可能で、請求書→見積書への参照が可能となっているレコードがありますが、これは仕様ですか？データ欠落ですか？
データ欠落だとすると、テーブル設計レベルで誤っていると思いますが。
また、上記のような場合に期待する出力はどんなものですか？
「1レコード毎に纏めて」の意味を定義してください。

[wasi300]

>> Orlsofskyさん
Insert文修正して、結果も書いてみます。ありがとうございます。

>> gentaroさん
ご指摘ありがとうございます。
見積書IDで参照できない請求書はあります。
請求書IDで見積の参照はできません。見積書の参照は見積IDで行います。
どのレコードか教えていただけますでしょうか。

「1レコード毎に纏めて」については、質問修正します。

[gentaro]

> と。書いていて普通にJOINして、削除フラグをプログラムから見て
> ボタンを出力する・しないすればいいだけだということに気が付きました。

これは問題が解決したという意味ですか？

[wasi300]

はい。解決しました。お騒がせしました。
顧客テーブルを考慮する必要がなかったことが抜けていました。
ありがとうございます。

[gentaro]

なるほど。
ではご自身で回答を作成し、自己解決としてクローズしてください。

[sazi]

> 引用テキスト上記実行結果で見積もりIDの重複が無くなれば期待通りになります。
本当ですか？　
invoiceのestimate_idでestimateを参照するとdeleted_atが設定されているもの（無効）のestimate_idも取得していますよ。
例えば、質問のSQLで取得される、
(estimate_id, invoice_id, customer_id)=(174710775,986426253,922361081)
についてestimateのデータはdeleted_atが設定されています。

Answer 1

見積書・請求書に顧客が紐づいていなくても出力したいです

見積書・請求書の顧客を使用するとして結合するかどうかは後回し。

見積書・請求書、どちらか一方のデータがあれば出力したいです

質問のSQLでは、Full joinが使用できない場合の、全外部結合の組み立てができていませんね。

A.請求書＋関連する見積書：請求書→(lefto join)→見積書
B.見積書のみ：見積書→(lefto join)→請求書で請求書が無いもの
全体に対してそれぞれが補集合です。
Full Joinを使わない場合は、補集合をunion ALL します。
※補集合なので重なりませんからunion allします。

  select  invoice.estimate_id, invoice.invoice_id, invoice.customer_id
  from  invoice 
        left join estimate 
        on    invoice.customer_id = estimate.customer_id
          and invoice.estimate_id = estimate.estimate_id
          and invoice.deleted_at is null
          and estimate.deleted_at is null
  where invoice.host_id = 1203507743
union all
  select  estimate.estimate_id, invoice.invoice_id, estimate.customer_id
  from  estimate 
        left join invoice 
        on    estimate.customer_id = invoice.customer_id
          and estimate.estimate_id = invoice.estimate_id
          and estimate.deleted_at is null
          and invoice.deleted_at is null
  where estimate.host_id = 1203507743
    and invoice.invoice_id is null

追記

削除フラグを取得後に判断する

  select  invoice.estimate_id, invoice.invoice_id, invoice.customer_id
        , invoice.deleted_at as invoice_deleted_at
        , estimate.deleted_at as estimate_deleted_at
  from  invoice 
        left join estimate 
        on    invoice.customer_id = estimate.customer_id
          and invoice.estimate_id = estimate.estimate_id
  where invoice.host_id = 1203507743
union all
  select  estimate.estimate_id, invoice.invoice_id, estimate.customer_id
        , invoice.deleted_at as invoice_deleted_at
        , estimate.deleted_at as estimate_deleted_at
  from  estimate 
        left join invoice 
        on    estimate.customer_id = invoice.customer_id
          and estimate.estimate_id = invoice.estimate_id
  where estimate.host_id = 1203507743
    and invoice.invoice_id is null

Comments

[wasi300]

SQL教えてくださりありがとうございます！

今回は、見積書・請求書をDeleteフラグ立ってても取得して、
プログラムで、Deleteフラグを見て、処理変えるようにします！

[sazi]

質問に追記されているSQLでも、全外部結合の結果になっていません。（請求書のみがありません）
そもそも、自分で必要なパターンのテストデータを作成していないから、見落としが生じているんだと思います。

[wasi300]

追記に書いたものでは、見積IDと連携したものしか結果を貰えないので不十分でした…。

ありがとうございます。