title: How do I consolidate these two subqueries?
body: <p>I am writing an e-commerce query that polls for users with specific roles.  The exact purpose of the query isn't important, all that matters is that it's working as intended right now.  However, I really would like to clean up this section of the query</p>
  
  <pre><code>.
  .
  .
  where u.firstname is not null
  and u.users_id in (
    select u.users_id from users u 
    inner join mbrrole mr on mr.member_id = u.users_id and mr.ORGENTITY_ID IN (
        select ORGENTITY_ID from po_org_tree_v
    )
    inner join roledesc rd on rd.role_id = mr.role_id 
    where 1=1 
    and rd.displayname IN ('Customer Service Representative',  'Account Representative')
  ) 
  and u.users_id in (
    select u.users_id from users u 
    inner join mbrrole mr on mr.member_id = u.users_id and mr.ORGENTITY_ID IN (
        select ORGENTITY_ID from po_org_tree_v
    )
    inner join roledesc rd on rd.role_id = mr.role_id 
    where 1=1 
    and rd.displayname = 'Registered Customer'
  );
  </code></pre>
  
  <p>As you can see, I have to nearly identical subqueries in the parent query's where clauses, where the only difference between then is the rd.displayname check.  Is there some way in sql to comine two like queries into a "function", similar to actual functions in programming languages?  How can I combine these subqueries to clean up the main query?</p>