A Conditional based Join


Here we are going to see how A Conditional based Join can be used using COALESCE()

A scneario may come in such a way that the value matching column in join may deponds on many columns in tables (e.g two tables ).If coulumn1 has value tn column two will be null and vice versa. In scenario like this COALESCE() plays important role..


SELECT ORG.NAME 'user organisation' FROM
USERSTABLE USERS
INNER JOIN USERIDENTIFIER USERID
ON USERID.USERSID = USERS.ID
AND USERS.ID = @USERID
INNER JOIN ORGANISATION ORG ON
ORG.ID = COALESCE(USERID.ORGANISATIONID , USERS.OWNORGANISATIONID) -- Line 7



The scenario here is that either USERID.ORGANISATIONID has value and USERS.OWNORGANISATIONID has null and vice versa. The Coalesece function plays a remarkable role here (line 7) and as it always returns non null expression. In any case the above query always will return organisation as a result and wont fail to return.


Related Articles

More articles: SQL Join

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: