Subqueries on Subqueries

SQL INNER JOINing 2 Subqueries

In order to make the query work, you need to name the inner selects (sub-queries):

SELECT * 
FROM
(
SELECT B.branchName, A.type, AVG (T.amount), COUNT(A.accNumber)
FROM Branch B, Account A, Transactions T
WHERE 
    B.branchNumber = A.branchNumber AND
    A.accNumber = T.accNumber
GROUP BY B.branchName, A.type
) q1

INNER JOIN

(
SELECT B1.branchName, COUNT(A1.accNumber)
FROM Account A1, Branch B1
WHERE 
      A1.branchNumber = B1.branchNumber 
GROUP BY B1.branchName
HAVING COUNT(A1.accNumber) > 5
) q2
ON
 q1.branchName = q2.branchName

SQLAlchemy Select from Join of two Subqueries

Not sure if this is the best solution but this is how I got it to work. Using 3 subqueries essentially.

query1 = db.session.query(
    GUEST_USERS.DATE_WEEK_START.label("DATE_1"),
    func.count(GUEST_USERS.QUESTIONS).label("QUESTIONS_CNT")
).filter(
    GUEST_USERS.LOCATION.like("%TEXAS%"),
    GUEST_USERS.DATE == "2021-08-08"
).group_by(GUEST_USERS.DATE)

query2 = db_session_stg.query(
    USERS.DATE.label("DATE_2"),
    func.count(USERS.ANSWERS).label("ANSWERS_CNT")
).filter(
    USERS.LOCATION.like("%TEXAS%"),
    USERS.DATE == "2021-08-08"
).group_by(USERS.DATE)

sq1 = query1.subquery()
sq2 = query2.subquery()

query3 = db.session.query(sq1, sq2).join(
    sq2,
    sq2.c.DATE_2 == sq1.c.DATE_1)
sq3 = query3.subquery()

query4 = db.session.query(
    func.coalesce(
        sq3.c.DATE_1, sq3.c.DATE_2),
    sq3.c.QUESTIONS_CNT,
    sq3.c.ANSWERS_CNT
)
results = query4.all()

Why to use Correlated Subqueries?

Oracle has a good optimizer, but correlated subqueries are sometimes the most efficient way to express a query. For instance:

select t.*,
       (select count(*) from z where z.t_id = t.id)
from t;

can be quite efficient with an index on z(t_id) because it avoids the outer aggregation.

There are other cases where they are both efficient and directly translate into a question: Fetch all the ts that don't exist in z.

select t.*
from t
where not exists (select 1 from z where z.id = t.id);

And finally, correlated subqueries are just an example of lateral joins. And lateral joins can be quite powerful. For instance, to get all the columns from the previous row, you might use:

select t.*, t2.*
from t cross join lateral
     (select t2.*
      from t t2
      where t2.date < t.date
      order by t2.date desc
      fetch first 1 row only
     ) t2;

How do I consolidate these two subqueries?

The code is checking that two different types of roles are available for each user.

Your current method might have the best performance, but you can simplify this down to one subquery:

where u.firstname is not null and
      u.users_id in (select mr.member_id
                     from mbrrole mr inner join
                          roledesc rd
                          on rd.role_id = mr.role_id
                     where mr.ORGENTITY_ID IN (select ORGENTITY_ID from po_org_tree_v) and
                           rd.displayname IN ('Registered Customer', 'Customer Service Representative',  'Account Representative')
                     group by mr.member_id
                     having sum(rd.displayname = 'Registered Customer') > 0 and
                            sum(rd.displayname IN ('Customer Service Representative',  'Account Representative')) > 0
                    ) 

Note the following changes:

• Removed users from the subquery. It is not needed.
• Moved the ORGENTITY_ID condition to the where clause.
• Added the GROUP BY and HAVING. The HAVING checks that both types of roles are defined for the user.

---

Related Topics