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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top