Question 89777 in PostgreSQL Question posted in
The official documentation can be found here.

All joined subquery results return null – Postgresql

I am trying to get all customers with their latest payment transaction, including customers without any transaction:

SELECT c.customer_id, c.phone_number, c.email
     , p.transaction_no, p.amount, p.transaciton_datetime
FROM tbl_customers c 
LEFT JOIN (
   SELECT customer_id, transaction_no, amount, transaciton_datetime
   FROM tbl_payment_transactions
   ORDER BY payment_transaction_id DESC
   LIMIT 1
) p
ON c.customer_id = p.customer_id

The above query returns NULL for p.transaction_no, p.amount, p.transaciton_datetime in every row. But I can make sure that there are transactions made by customers in tbl_payment_transactions.

Tags:

2

Answers


  1. 0

    You want the subquery to be run once per each different row of the driving table tbl_customers. This is called a lateral subquery and takes the form:

    SELECT
  c.customer_id, c.phone_number, c.email,
  p.transaction_no, p.amount, p.transaciton_datetime
FROM tbl_customers c 
LEFT JOIN LATERAL (
  SELECT customer_id, transaction_no, amount, transaciton_datetime
  FROM tbl_payment_transactions t
  WHERE c.customer_id = t.customer_id
  ORDER BY payment_transaction_id DESC 
  LIMIT 1
) p
ON true
    Login or Signup to reply.
  2. 0

    The Impaler provided the correct form with a LATERAL subquery.

    Alternatively, you can use DISTINCT ON in a subquery and a plain LEFT JOIN.

    Performance of the latter can be better while retrieving all (or most) customers, and if there are only few transactions per customer and/or you don’t have a multicolumn index on (customer_id, payment_transaction_id) or (customer_id, payment_transaction_id DESC):

    SELECT c.customer_id, c.phone_number, c.email
     , p.transaction_no, p.amount, p.transaciton_datetime
FROM   tbl_customers c 
LEFT   JOIN (
   SELECT DISTINCT ON (customer_id)
          customer_id, transaction_no, amount, transaciton_datetime
   FROM   tbl_payment_transactions
   ORDER  BY customer_id, payment_transaction_id DESC
   ) p USING (customer_id);

    About performance aspects:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search