How to combine 4 sql queries into a single query with good performance?

Question

I have a problem to solve. First I split this problem into parts and so I wrote four queries separately but now I need to put them together as if it were a single call to return a single result. How can I do this?

1) I select purchases according to branch and store

SELECT CD_PURCHASE FROM TB_PURCHASE_STORE WHERE CD_BRANCH = ? AND CD_STORE = ?

2) I validate if the promotional period of the purchase is within the current date (today)

SELECT CD_PURCHASE, DT_BEGIN_PROMOTION, DT_END_PROMOTION FROM TB_PURCHASE 
WHERE SYSDATE BETWEEN TO_DATE(DT_BEGIN_PROMOTION) AND TO_DATE(DT_END_PROMOTION)
   

3) From the purchase code, I check which products are active

SELECT CD_PURCHASE, CD_PRODUCT FROM TB_PURCHASE_PRODUCT WHERE FL_ACTIVE = 1

4) Finally, I return some fields according to the customer id

SELECT CD_PURCHASE, CD_PRODUCT, ID_CUSTOMER, DT_LAST_PURCHASE 
FROM TB_PURCHASE_SALES WHERE ID_CUSTOMER = ?

Answer 1

Have you tried below query? I've assumed you want INNER JOIN for all the tables, and CD_PURCHASE is common link in all the tables, and CD_PRODUCT is the link between TB_PURCHASE_PRODUCT and TB_PURCHASE_SALES.

SELECT TPS.CD_PURCHASE,
TP.CD_PURCHASE, TP.DT_BEGIN_PROMOTION, TP.DT_END_PROMOTION, 
TPP.CD_PURCHASE, TPP.CD_PRODUCT,
TPSS.CD_PURCHASE, TPSS.CD_PRODUCT, TPSS.ID_CUSTOMER, TPSS.DT_LAST_PURCHASE
FROM TB_PURCHASE_STORE TPS, 
TB_PURCHASE TP, 
TB_PURCHASE_PRODUCT TPP,
TB_PURCHASE_SALES TPSS
WHERE TPS.CD_BRANCH = ? AND TPS.CD_STORE = ?
AND TPS.CD_PURCHASE = TP.CD_PURCHASE
AND SYSDATE BETWEEN TO_DATE(TP.DT_BEGIN_PROMOTION) AND TO_DATE(TP.DT_END_PROMOTION)
AND TPP.CD_PURCHASE = TPS.CD_PURCHASE
AND TPP.FL_ACTIVE = 1
AND TPSS.CD_PURCHASE = TPS.CD_PURCHASE AND TPSS.CD_PRODUCT = TPP.CD_PRODUCT
AND TPSS.ID_CUSTOMER = ?