I have a query where the last table security_role_authorization needs to be rewritten using a left join somehow so that if there is no authorization level for a security role it will still show the security roles:
select br.BUSN_ROLE_NM "Administration", br.BUSN_ROLE_CD "Business role code", br.BUSN_ROLE_TYP_CD "Type of role", br.BUSN_ROLE_VOID_IND "Void index", e.EMPL_ID "Emloyee Id", e.EMPL_DRV_NM "Name", sr1.SEC_ROLE_NM "Security Role", sra.sec_role_auth_max "Emp Claim Level", b.EMPL_DRV_NM "Supervisor", auth_typ_cd from employee e, employee b, employee_relationship er, business_role br, business_security_role bsr, security_role sr1, employee_business_role ebr, security_role_authorization sra where e.EMPL_ID = ebr.EMPL_ID and e.EMPL_ID = er.EMPL_ID and b.EMPL_ID = er.EMPL_ID_RLT and bsr.SEC_ROLE_CD = ebr.SEC_ROLE_CD and ebr.SEC_ROLE_CD = sr1.SEC_ROLE_CD and br.busn_role_cd = bsr.BUSN_ROLE_CD and br.busn_role_cd = ebr.BUSN_ROLE_CD and sra.SEC_ROLE_CD = bsr.SEC_ROLE_CD and sra.SEC_ROLE_CD = ebr.SEC_ROLE_CD and sra.SEC_ROLE_CD = sr1.SEC_ROLE_CD order by 6,7
Thanks in advance.
This is a marvellous example of why JOIN syntax is better than old style joins using a WHERE clause. Just see how much easier this is to understand:
select ... from employee e inner join employee_relationship er on e.EMPL_ID = er.EMPL_ID inner join employee b on er.EMPL_ID_RLT = b.EMPL_ID inner join employee_business_role ebr on e.EMPL_ID = ebr.EMPL_ID inner join business_role br on ebr.BUSN_ROLE_CD = br.busn_role_cd inner join business_security_role bsr on br.busn_role_cd = bsr.BUSN_ROLE_CD and ebr.SEC_ROLE_CD = bsr.SEC_ROLE_CD inner join security_role sr1 on ebr.SEC_ROLE_CD = sr1.SEC_ROLE_CD left outer join security_role_authorization sra on sr1.SEC_ROLE_CD = sra.SEC_ROLE_CD
Notice how each additional join uses only columns from tables already mentioned earlier in the FROM clause, and how the columns from the next table being joined are always on the right side of the join conditions. This makes it trivially easy to check the join columns against the table aliases of previously mentioned tables for correctness.
If you didn't write these queries, and were trying to find a logical error in how the tables were being joined, which version would you rather work with?
This was first published in July 2005