SQL with句を利用して複数のテーブルのデータを表示させる方法

Question

前提・実現したいこと

複数のテーブルをサブクエリで結合し、且つWith句を利用してデータを取得しようとしたところ、
データがnullになってしまう現象が発生します。どのようにSQLを記載すれば問題が解決できますでしょうか。
お知恵をおかしください。

メインテーブル as メイン
商品ID, 仕入れ日

配送テーブル as 配送
商品ID, 配送ID

配送詳細テーブル as 配送詳細
配送ID, 配送サービス名, 更新日

このようなテーブルがあり、各商品ID毎の配送サービス名を表示したいです。
配送詳細テーブルは、配送サービスを変更するごとにその時の更新日とともに行数が増えてしまうので、
１商品IDに対して一つの配送サービス名が表示されるようなSQLを書きたいです。
※配送サービスを更新する毎に下記のようになってしまうので、
「補足」配送サービス毎に配送IDが異なります。
配送ID, 配送サービス名, 更新日
12345,ヤマト, 2020-01-01
32145, 佐川, 2019-12-01

可能な限り下記のベースとなるWith句を用いて記載したいです。

該当のソースコード

with 配送情報 as
(
Select 配送.商品ID, 配送詳細.配送サービス名, 配送詳細.更新日
from 配送テーブル as 配送

left join 配送詳細テーブル as 配送詳細
on 配送.配送ID = 配送詳細.配送ID
order by 配送詳細.更新日 DESC limit 1
)

SELECT メイン.商品ID, 配送情報.配送サービス名
from メインテーブル as メイン
LEFT JOIN 配送情報 on
配送情報.商品ID = メイン.商品ID

上記コードの結果

上記コードを走らせると、
なぜか

メイン.商品ID, 配送情報.配送サービス名
xxxxxx123, null

というデータがかえってきます。

with句のなかに下記のように商品IDを特定すると結果はしっかり帰ってきます。

with 配送情報 as
(
Select 配送.商品ID, 配送詳細.配送サービス名, 配送詳細.更新日
from 配送テーブル as 配送

left join 配送詳細テーブル as 配送詳細
on 配送.配送ID = 配送詳細.配送ID
where 配送.商品ID = xxxxxx123
order by 配送詳細.更新日 DESC limit 1
)

ただデータを取得する際には大量な商品IDのデータを取得するので、
With句内のwhereで商品IDを指定することはできない状況です。

この問題を解決するにはどのようなSQLを記載すべきでしょうか。

Answer 1

limit 1を指定しているので、商品IDに関わらず、1件しか取得できていません。
商品ID毎に1件になるようにする必要があります。

以下は相関副問合せによるnot exists判定で、商品ID毎に配送詳細テーブルの更新日が最大のものに限定しています。

with 配送情報 as (
  Select  配送.商品ID, 配送詳細.配送サービス名, 配送詳細.更新日
  from  配送テーブル as 配送
        left join 配送詳細テーブル as 配送詳細
        on  配送.配送ID = 配送詳細.配送ID
  where not exists(
          select 1 
          from  配送テーブル
                left join 配送詳細テーブル
                on  配送テーブル.配送ID = 配送詳細テーブル.配送ID
          where 配送テーブル.商品ID = 配送.商品ID
            and 配送詳細テーブル.更新日 > 配送詳細.更新日
        )
)
SELECT メイン.商品ID, 配送情報.配送サービス名
from  メインテーブル as メイン
      LEFT JOIN 配送情報 
      on  配送情報.商品ID = メイン.商品ID

Comments

[sabasoba]

ありがとうございます。
頂いたコード内に、配送詳細.商品IDとありますが、

where 配送テーブル.商品ID = 配送詳細.商品ID

配送詳細テーブルには商品IDの情報がございませんので、エラーとなってしまいました。
この部分考慮した場合、どのように記載することがよろしいでしょうか。。。

[sazi]

訂正しました。
因みに、更新日は商品ID毎にユニークであることが前提です。

[sabasoba]

ありがとうございます。完璧に実現できました。