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 = ?
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 = ?