CODEWITHSUNDEEP
mysqlsql
Mazzy
Mazzy

Reputation: 327

Only show rows where mysql subquery returns null

I am trying to query a receipts table, to pull order numbers from a database who's first transaction falls within a certain time range. Here is what I have so far:

SELECT id, order_number, 
(
  SELECT id FROM receipts 
  WHERE received < r.received AND order_number = r.order_number 
  LIMIT 1
) priorPayment 
FROM receipts r 
WHERE received > 1416384000 AND received < 1416470399 
ORDER BY id DESC

This works fine except I want to filter out anything that has a priorPayment returned (so only show where this subquery returns null) right now it returns everything. Any suggestions?

Upvotes: 0

Views: 4478

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Although you can use a subquery for this or a having clause, I would recommend simply using a left join

SELECT r.id, r.order_number
FROM receipts r LEFT JOIN
     receipts r2
     ON r2.received < r.received and r2.order_number = r.order_number
WHERE r.received > 1416384000 AND r.received < 1416470399 AND
      r2.order_number is NULL
ORDER BY r.id DESC;

Upvotes: 3

Marcus Adams
Marcus Adams

Reputation: 53870

Here's a common way to write that:

SELECT r.id, r.order_number
FROM receipts r
WHERE r.received > 1416384000 AND r.received < 1416470399  
AND NOT EXISTS (SELECT id FROM receipts WHERE received < r.received AND order_number = r.order_number LIMIT 1)
ORDER BY r.id DESC

Alternatively, you can use an anti-join in this case:

SELECT r1.id, r1.order_number
FROM receipts r1
LEFT JOIN receipts r2
  ON r2.order_number = r1.order_number AND r2.received < r1.received
WHERE r1.received > 1416384000 AND r1.received < 1416470399
  AND r2.order_number is NULL
ORDER BY r1.id DESC

Upvotes: 1

Zsuzsa
Zsuzsa

Reputation: 427

For better performance I would recommand to keep the sub-query (which selects only the 3 columns you need) and combine it, with the LEFT JOIN and the IS NULL condition to get the expected result:

SELECT r.id, r.order_number
FROM receipts r 
left join (
SELECT id, received, order_number FROM receipts 
) priorPayment
ON priorPayment.received < r.received 
AND priorPayment.order_number = r.order_number
WHERE r.received > 1416384000 
AND r.received < 1416470399 
AND r.priorPayment.id is null
ORDER BY r.id DESC

Upvotes: 1

void
void

Reputation: 7890

another way is just bring the subquery from select into where clause:

SELECT id, order_number, null as priorPayment 
FROM receipts r 
WHERE received > 1416384000 AND received < 1416470399 
and (
  SELECT id FROM receipts 
  WHERE received < r.received AND order_number = r.order_number 
  LIMIT 1
) is null
ORDER BY id DESC

Upvotes: 1

Related Questions