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",
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
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

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
  join employee_relationship er
    on e.EMPL_ID = er.EMPL_ID
  join employee b
   on er.EMPL_ID_RLT = b.EMPL_ID
  join employee_business_role ebr
    on e.EMPL_ID = ebr.EMPL_ID
  join business_role br
    on ebr.BUSN_ROLE_CD = br.busn_role_cd
  join business_security_role bsr
    on br.busn_role_cd = bsr.BUSN_ROLE_CD
   and ebr.SEC_ROLE_CD = bsr.SEC_ROLE_CD
  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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: