Is there any other alternative for the given query? I don't want to user left outer join in the query. It is giving me some problem on the developement side of my project. 

SELECT 
A.USER_ID,A.FK_ROLE_ID,B.FIRST_NM,
B.LAST_NM,B.ADD_LINE_1,B.ADD_LINE_2,
B.STATE,B.CITY,B.ZIP,B.PK_PARENT_ID,C.ROLE_NM 
FROM CHATHAM_USERS A LEFT OUTER JOIN PARENT B 
ON A.FK_PARENT_ID=B.PK_PARENT_ID,ROLE C 
WHERE A.FK_ROLE_ID=C.PK_ROLE_ID AND
C.PK_ROLE_ID IN (1,2,3) --AND
--B.ACTV_REC_IN = 'Y'

    Requires Free Membership to View

    Login

The first thing to notice is that there might be a problem in how the query executes (depending on which database system you're using). You're mixing JOIN syntax with the "old school" table list syntax which uses a comma-separated list of tables in the FROM clause and join conditions in the WHERE clause.

Let's rewrite the query using only JOIN syntax:

select A.USER_ID
     , A.FK_ROLE_ID
     , B.FIRST_NM
     , B.LAST_NM
     , B.ADD_LINE_1
     , B.ADD_LINE_2
     , B.STATE
     , B.CITY
     , B.ZIP
     , B.PK_PARENT_ID
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
left outer 
  join PARENT B 
    on A.FK_PARENT_ID
     = B.PK_PARENT_ID

Now, let's address your original question. You want to avoid using a LEFT OUTER JOIN. To be honest, I cannot see why. But to answer your question, there is a way to achieve the same results, by using a UNION query of an INNER join and a NOT EXISTS subquery.

select A.USER_ID
     , A.FK_ROLE_ID
     , B.FIRST_NM
     , B.LAST_NM
     , B.ADD_LINE_1
     , B.ADD_LINE_2
     , B.STATE
     , B.CITY
     , B.ZIP
     , B.PK_PARENT_ID
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
inner

  join PARENT B 
    on A.FK_PARENT_ID
     = B.PK_PARENT_ID
union all     
select A.USER_ID
     , A.FK_ROLE_ID
     , null
     , null
     , null
     , null
     , null
     , null
     , null
     , null
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
 where not exists
     ( select *
         from PARENT B 
        where PK_PARENT_ID
            = A.FK_PARENT_ID )

Why would someone want to use the UNION and NOT EXISTS instead of a LEFT OUTER JOIN? No idea.

But wait -- there's more.

In your original query, there's a condition on the B table that is commented out. Is this the real source of your problems? When you uncomment that condition in the WHERE clause, guess what happens -- you no longer return any unmatched rows, correct? That's because in a LEFT OUTER JOIN, conditions on the right table (the B table in this case) must be placed in the ON clause of the join, and not the WHERE clause.

select A.USER_ID
     , A.FK_ROLE_ID
     , B.FIRST_NM
     , B.LAST_NM
     , B.ADD_LINE_1
     , B.ADD_LINE_2
     , B.STATE
     , B.CITY
     , B.ZIP
     , B.PK_PARENT_ID
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
left outer 
  join PARENT B 
    on A.FK_PARENT_ID
     = B.PK_PARENT_ID
   and B.ACTV_REC_IN = 'Y'

If you do not understand the difference -- yes, it's subtle -- between a LEFT OUTER JOIN ON condition and a WHERE condition, please post another question and I'll explain it.

This was first published in April 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