9-12 一対多の外部結合

SQLパズル

WorkFlowテーブル               Personnelテーブル
job  auth1  auth2  auth3       emp_id  fname
---  -----  -----  -----       ------  -----
  A      a      b      c          a    Joe
  B      a      b      c          b    Mary
  C      b   null      d          c    Ann
  D   null   null   null          d    Bill

WorkFlowテーブルのJobごとのauth1,auth2,auth3
をPersonnelテーブルのemp_idと結合させて、
行に変換して出力する

出力結果
job  authorized_by
---  -------------
  A  Joe
  A  Mary
  A  Ann
  B  Joe
  B  Mary
  B  Ann
  C  Mary
  C  Bill
  D  null

データ作成スクリプト

create table WorkFlow(
job   char(10),
auth1 char(1),
auth2 char(1),
auth3 char(1));

insert into WorkFlow values('A','a' ,'b' ,'c');
insert into WorkFlow values('B','a' ,'b' ,'c');
insert into WorkFlow values('C','b' ,null,'d');
insert into WorkFlow values('D',null,null,null);

create table Personnel(
emp_id char(1),
fname  char(4));

insert into Personnel values('a','Joe');
insert into Personnel values('b','Mary');
insert into Personnel values('c','Ann');
insert into Personnel values('d','Bill');
commit;

SQL

select a.job,b.fname
  from WorkFlow a Left Join Personnel b
    on b.emp_id in(a.auth1,a.auth2,a.auth3)
order by a.job,b.emp_id;

解説

emp_idがauth1,auth2,auth3のいずれかと等しいことを結合条件として、
一対多の外部結合を行ってます

---