8-17 SubStoreも出力

SQLパズル

UserTable               AdmissionTable
UserID  AdmissionID     AdmissionID  StoreCode
------  -----------     -----------  ----------
UserAA  Admission11     Admission11  StoreAAAAA
UserBB  Admission22     Admission11  StoreBBBBB
UserCC  Admission33     Admission22  StoreBBBBB
                        Admission22  StoreCCCCC
                        Admission33  StoreEEEEE

StoreTable
StoreCode   StoreName   BelongStoreName
----------  ----------  ---------------
StoreAAAAA  StoreAMain  StoreAMain
StoreBBBBB  StoreASub   StoreAMain
StoreCCCCC  StoreBMain  StoreBMain
StoreDDDDD  StoreBSub   StoreBMain
StoreEEEEE  StoreCMain  StoreCMain
StoreFFFFF  StoreCSub   StoreCMain

UserTableのUserIDごとに、
AdmissionIDがふられていて、
AdmissionIDで、閲覧可能なStoreTableのStoreCodeがAdmissionTableに保存されています。

ユーザごとに閲覧可能なStoreTableのデータを以下の形式で出力します。
ただし、MainStoreを閲覧可能な場合は、SubStoreも閲覧可能とします。
例えば、StoreAMainを閲覧可能ならStoreASubも閲覧可能ということです。

出力結果
UserID  AdmissionID  StoreCode   StoreName   BelongStoreName
------  -----------  ----------  ----------  ---------------
UserAA  Admission11  StoreAAAAA  StoreAMain  StoreAMain
UserAA  Admission11  StoreBBBBB  StoreASub   StoreAMain
UserBB  Admission22  StoreBBBBB  StoreASub   StoreAMain
UserBB  Admission22  StoreCCCCC  StoreBMain  StoreBMain
UserBB  Admission22  StoreDDDDD  StoreBSub   StoreBMain
UserCC  Admission33  StoreEEEEE  StoreCMain  StoreCMain
UserCC  Admission33  StoreFFFFF  StoreCSub   StoreCMain

データ作成スクリプト

create table UserTable as
select 'UserAA' as UserID,'Admission11' as AdmissionID from dual
union select 'UserBB','Admission22' from dual
union select 'UserCC','Admission33' from dual;

create table AdmissionTable as
select 'Admission11' as AdmissionID,'StoreAAAAA' as StoreCode from dual
union select 'Admission11','StoreBBBBB' from dual
union select 'Admission22','StoreBBBBB' from dual
union select 'Admission22','StoreCCCCC' from dual
union select 'Admission33','StoreEEEEE' from dual;

create table StoreTable as
select 'StoreAAAAA' as StoreCode,'StoreAMain' as StoreName,'StoreAMain' as BelongStoreName from dual
union select 'StoreBBBBB','StoreASub' ,'StoreAMain' from dual
union select 'StoreCCCCC','StoreBMain','StoreBMain' from dual
union select 'StoreDDDDD','StoreBSub' ,'StoreBMain' from dual
union select 'StoreEEEEE','StoreCMain','StoreCMain' from dual
union select 'StoreFFFFF','StoreCSub' ,'StoreCMain' from dual;

SQL

select distinct UserID,AdmissionID,StoreCode,StoreName,BelongStoreName
from (select a.UserID,a.AdmissionID,
      c.StoreCode,c.StoreName,c.BelongStoreName,
      case when b.StoreCode = c.StoreCode
           then 1 else 0
           end as check1,
      max(case when b.StoreCode = c.StoreCode and c.BelongStoreName = c.StoreName
               then 1 else 0 end) over(partition by UserID,BelongStoreName) as check2
      from UserTable a,AdmissionTable b,StoreTable c
      where a.AdmissionID = b.AdmissionID)
where 1 in (check1,check2)
order by UserID,StoreCode;

解説

あえてStoreTableとクロスジョインさせてから、
分析関数を使ってます。

---