5-15 クロスジョインで求めた集合と外部結合

SQLパズル

会議テーブル
会議番号（PK)    会議名
-------------   --------
1               部長会議
2               役員会議

組織テーブル
組織コード(PK)   組織名
-------------   --------
A                管理部
B                営業部
C                購買部

会議資料テーブル （資料のヘッダー情報）
会議番号(PK)     組織コード(PK)    提出日付
------------    --------------    ---------------------
1               A                 2003/04/05
1               B                 null（nullは作成中）

各組織の会議用資料提出状況を出力する。

出力結果
会議番号   組織コード   提出状況   提出日付
-------   ----------   --------   ----------
1          A            提出済     2003/04/05
1          B            未提出     null
1          C            未提出     null
2          A            未提出     null
2          B            未提出     null
2          C            未提出     null

データ作成スクリプト

create table 会議(
会議番号 char(1),
会議名 char(8),
primary key (会議番号));

create table 組織(
組織コード char(1),
組織名 char(6),
primary key (組織コード));

create table 会議資料(
会議番号 char(1),
組織コード char(1),
提出日付 date,
primary key (会議番号,組織コード));

insert into 会議(会議番号,会議名) values('1','部長会議');
insert into 会議(会議番号,会議名) values('2','役員会議');
insert into 組織(組織コード,組織名) values('A','管理部');
insert into 組織(組織コード,組織名) values('B','営業部');
insert into 組織(組織コード,組織名) values('C','購買部');
insert into 会議資料(会議番号,組織コード,提出日付) values('1','A',to_date('20030405','yyyymmdd'));
insert into 会議資料(会議番号,組織コード,提出日付) values('2','B',null);
commit;

SQL

--■■■case式を使用する方法■■■
select a.会議番号,b.組織コード,
case when exists(select 1 from 会議資料 c
                  where c.会議番号=a.会議番号
                    and c.組織コード=b.組織コード
                    and c.提出日付 is not null)
     then '提出済' else '未提出' end as 提出状況,
(select c.提出日付 from 会議資料 c
  where c.会議番号=a.会議番号
    and c.組織コード=b.組織コード) as 提出日付
from 会議 a,組織 b
order by a.会議番号,b.組織コード;

--■■■クロスジョインと外部結合を使用する方法■■■
select a.会議番号,b.組織コード,
nvl2(c.提出日付,'提出済','未提出') as 提出状況,
c.提出日付
from 会議 a cross join 組織 b
Left join 会議資料 c
on a.会議番号=c.会議番号 and b.組織コード=c.組織コード
order by a.会議番号,b.組織コード;

解説

3つ以上のテーブルを結合させる際に、SQL99の結合の構文は便利です。

---