PostgreSQL SELECT WHERE IN Subquery with its own condition from other column

Question

I have a (temporary) table (let's just call it param) with records like shown below

BranchID |       DateFrom      |       DateTo
   154   | 2019-01-01 00:00:00 | 2019-01-01 23:59:59
   361   | 2019-01-01 00:00:00 | 2019-01-01 23:59:59
   362   | 2019-01-01 10:00:00 | 2019-01-02 09:59:59

now if I want to select all of transactions that is done by those branches i can do like

SELECT *
FROM "Transaction" t
WHERE t."BranchID" IN (SELECT "BranchID" FROM param)

but i want my select to be restricted only for those datefrom and dateto. if however the datefrom and dateto is universal for every branch we can do something like

SELECT *
FROM "Transaction" t
WHERE t."BranchID" IN (SELECT "BranchID" FROM param)
AND t."TransactionDate" >= '2019-01-01 00:00:00'::TIMESTAMP
AND t."TransactionDate" <= '2019-01-01 23:59:59'::TIMESTAMP

but the problem is each branch has their own end of days (like shown in table param). is it possible to do it in one query like above? if possible i dont want to process it in php like doing foreach from table param and do the query for transaction for each row (branch) because that'll increase execution time (especially when we have more than 100 branches) so I want to make 1 big select for all branches and then I grouping it using PHP

Kaushik Nayak · Accepted Answer · 2019-02-03 16:21:09Z

2

You may use a JOIN.

SELECT t.*
 FROM "Transaction" t JOIN param p 
 ON p."BranchID" = t."BranchID"
AND t."TransactionDate" >= p.DateFrom
AND t."TransactionDate" <= p.DateTo

An EXISTS should also work fine.

SELECT t.*
     FROM "Transaction" t WHERE EXISTS 
( SELECT 1 FROM param p 
     WHERE p."BranchID" = t."BranchID"
    AND t."TransactionDate" >= p.DateFrom
    AND t."TransactionDate" <= p.DateTo
)

answered Feb 3, 2019 at 16:21

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL SELECT WHERE IN Subquery with its own condition from other column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related