1

I have the following query:

SELECT * 
FROM item_ledger 
WHERE to_id='7' 
AND (SELECT SUM(qty) AS qty_in 
     FROM item_ledger 
     WHERE from_item_ledger_id='5') < intransit_qty

the query works as expected. From that query, I also want to return qty_in to the main query. How to do that?

2

The subquery is uncorrelated, so you can simply make it a derived table (moving it to the FORM clause) or make it a CTE:

-- derived table
SELECT il.*, q.qty_in 
FROM item_ledger AS il
  CROSS JOIN
    (SELECT SUM(qty) AS qty_in 
     FROM item_ledger 
     WHERE from_item_ledger_id = '5') AS q
WHERE il.to_id = '7' 
  AND q.qty_in < il.intransit_qty ;

-- common table expression
WITH q AS
    (SELECT SUM(qty) AS qty_in 
     FROM item_ledger 
     WHERE from_item_ledger_id = '5')
SELECT il.*, q.qty_in 
FROM item_ledger AS il
  CROSS JOIN q
WHERE il.to_id = '7' 
  AND q.qty_in < il.intransit_qty ;

You could also use an inline subquery or a LATERAL join (available in 9.3+ versions) but those solutions would be more useful if the subquery were correlated.

  • cool, thanks for the help. I was made it like SELECT (SELECT SUM(qty) FROM item_ledger WHERE from_item_ledger_id='5') AS qty_in, * FROM item_ledger WHERE to_id='7' AND (SELECT SUM(qty) FROM item_ledger WHERE from_item_ledger_id='5') < intransit_qty..but I think your solution is better than mine...i'll use it. thanks again! – Dariel Pratama Nov 2 '15 at 10:29
  • Yeah, that (inline subquery) would be one more way to do this. I don't like it particularly because the code would have to be duplicated - although Postgres optimizer is good enough to identify it and only run it once. – ypercubeᵀᴹ Nov 2 '15 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.