Right outer join on two tables where ON clause has a subquery

Question

I am trying to perform a right outer join on two liferay tables — users_ and expandovalue — to get a result set.

When I did the following query on all the users, I got desired result.

SELECT USER_.FIRSTNAME
     , USER_.LASTNAME
     , USER_.EMAILADDRESS
     , USER_.JOBTITLE
     , expandovalue.data_  
from expandovalue 
     right outer join 
     user_ on expandovalue.classpk = user_.userid 
     and expandovalue.columnid =35695;

When tried to do the same for a set of users (part of a user group) it errored out. Query below:

SELECT USER_.FIRSTNAME
     , USER_.LASTNAME
     , USER_.EMAILADDRESS
     , USER_.JOBTITLE
     , expandovalue.data_ 
from expandovalue 
     right outer join
       user_ on expandovalue.classpk 
     in (select userid 
          from user_ 
         where userid 
                in ( select userid 
                      from users_usergroups
                     where usergroupid = 40073
                   ) and status =0) =   user_.userid in
                                             (select userid 
                                                from user_ 
                                               where userid 
                                                  in ( select userid 
                                                       from users_usergroups 
                                                     where usergroupid = 40073
                                                     )
                                              and status =0
                                            ) and expandovalue.columnid =35695

Here's the subquery which gives the userids of people in a particular usergroup.

(select userid 
   from user_ 
  where userid in 
                 ( select userid 
                     from users_usergroups
                    where usergroupid = 40073) 
                      and status =0
                 )

Am I going in a completely incorrect direction? Please advise.

1. Your query is missing an 'AND': ...and status =0) = Smile **AND** user_.userid in... 2. Which database are you using? — lleite, Sep 12 '12 at 16:46
USing Oracle 10g at the backend. Where exactly is an and missing. Thanks lleite — user1666444, Sep 12 '12 at 17:41
A good idea for posting is to include the error message. Note for next time. Welcome to Stack Overflow — RichardTheKiwi, Oct 02 '12 at 10:10

lleite · Answer 1 · 2012-09-13T16:58:52.443

Try this query:

SELECT USER_.FIRSTNAME,
       USER_.LASTNAME,
       USER_.EMAILADDRESS,
       USER_.JOBTITLE,
       expandovalue.data_
  from expandovalue
  right outer join user_
    on expandovalue.classpk IN
          (select userid
             from user_
             where userid in (select userid
                                from users_usergroups
                                where usergroupid = 40073) and 
                   status = 0) AND
       user_.userid in (select userid
                          from user_
                          where userid in (select userid
                                             from users_usergroups
                                             where usergroupid = 40073) AND
                                status =0) AND
       expandovalue.columnid = 35695

There was an "AND" missing after the end of the first subquery.

You should remove the "= Smile" from the above. The OP removed this text from the original question in an edit (click on the edit time for the question to see the edit history) but apparently you were already looking at the question when it was edited. With the "= Smile" removed I suspect it will work. Thanks. — Bob Jarvis - Слава Україні, Sep 13 '12 at 11:48

Right outer join on two tables where ON clause has a subquery

1 Answers1