CODEWITHSUNDEEP
oracle-databaserecursionplsqloracle-apexconnect-by
GuavaKhan
GuavaKhan

Reputation: 187

SQL: Recursion With a Join Confusion

I'll start by laying out what I'm trying to do and then I'll list the code I've made so far.

I'm coding in Oracle PL/SQL on the Application Express Platform

I have two tables: USERS and LEADS.

Leads Columns: LEADID, COMPANYNAME, CONTACTNAME, OWNER

Users Columns: EMAIL, SUPER, ROLE

Foreign Keys:

OWNER is a foreign key that refers to EMAIL in USERS

SUPER is a foreign key that refers to EMAIL in USERS

SUPER is the supervisor of a given person. ROLE is their position in the company

There are about 5 levels. 'PEON','MGR','DIR','SRDIR','VP'

Peons are the only people with leads assigned to them.

I'm trying to generate a report that returns rows containing the following SUBORDINATE, NUMLEADS

Subordinate is anyone directly under the user using the application. I have code for that

select U.EMAIL as Subordinate
from USERS U
WHERE lower(v('APP_USER')) = U.SUPER

Numleads is all the leads created by peons under the subordinate's organization. I currently have code to list the number of peons under the current user

select count(*)
from USERS U2
where  U2.ROLE = 'PEON'
start with lower(v('APP_USER')) = U2.EMAIL
connect by NOCYCLE prior U2.email = U2.super

I'm part of the way there, but I'm confused how to reference the result of a query in a recursive sequence. I know I need to query all PEONS under the subordinates of the current user, JOIN them with all leads they're associated with, and then count the number of leads. But i'm not sure how to order that in SQL.

Your help is much appreciated

EDIT: Answer figured out thanks to JBrooks

   select U.EMAIL as Sub, count(*) as CreatedAllTime
   from USERS U
   left join USERS UPEON
   on UPEON.EMAIL in
   (
   select UPEON2.EMAIL
   from USERS UPEON2
   where  UPEON2.ROLE = 'PEON'
   start with U.EMAIL = UPEON2.EMAIL
   connect by NOCYCLE prior UPEON2.email = UPEON2.super
   )
   left join LEADS L
   on UPEON.EMAIL = L.OWNER
   where U.EMAIL in
   (
   select U2.EMAIL as Sub
   from USERS U2
   WHERE lower(v('APP_USER')) = U2.SUPER
   )
   group by U.EMAIL

Upvotes: 1

Views: 201

Answers (1)

JBrooks
JBrooks

Reputation: 10013

    select U2.Email as Subordinate,
    count(*) as NumLeads
    from USERS U2
    left join LEADS l
    on U2.Email = l.Owner
    where U2.ROLE = 'PEON'
    and lower(v('APP_USER')) in
        (select EMAIL
        from USERS S
        START WITH lower(v('APP_USER')) = lower(S.SUPPER)
        CONNECT BY PRIOR EMAIL = SUPPER)
    group by U2.Email
    order by U2.Email

Upvotes: 2

Related Questions