CODEWITHSUNDEEP
mysql
Leandro Dimitrio
Leandro Dimitrio

Reputation: 112

Get latest entry in MySQL if it meets criteria

I'm finishing up a system for buying event tickets, and I need to be able to check if there are still tickets available.

I have a table (tbl_rel_order_status) that makes a relation between orders and new statuses received from a payment gateway (like, say, PayPal). Every new transaction generates a new entry.

Structure and example for tbl_rel_order_status (tbl_order_id and tbl_status_order_id are FKs)

| id | tbl_order_id | tbl_status_order_id | datetime            |
| 1  | 1            |         1           | 2016-08-01 18:20:00 |
| 2  | 1            |         3           | 2016-08-01 18:20:00 |

Structure and example for tbl_order (tbl_ticket_id, tbl_client_id and tbl_payment_method_id are FKs)

| id | tbl_ticket_id | tbl_client_id | tbl_payment_method_id | qnty |
| 1  | 1             |         1     | 1                     |  40  |
| 2  | 1             |         2     | 1                     |  3   |

I have a few "cancellation statuses" (namely: 6,7,8,11,13,15), which all mean that for some reason that amount of tickets can be made available again for other clients.

Here's the thing: I'm working on a query to retrieve the quantities of tickets which have as latest status any of the mentioned above. If not, the query should skip to the next ticket (that's where I'm stuck).

SELECT tbl_order.qnty
FROM (SELECT tbl_order_id,tbl_status_order_id
      FROM tbl_rel_order_status
      WHERE tbl_status_order_id IN ('6','7','8','11','13','15')
      ORDER BY id DESC) inside
INNER JOIN tbl_order on tbl_order.id = inside.tbl_order_id
GROUP BY tbl_status_order_id

With that query I can only get the ones that DO have any of these, but not for the latest (or newest) DB entry.

SQL Fiddle

Any clues?

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can get the latest status using a query like this:

select o.*,
       (select ros.tbl_status_order_id
        from tbl_rel_order_status ros
        where ros.tbl_order_id = o.id
        order by ros.datetime desc
        limit 1
       ) as last_status
from tbl_order o;

From this, I think you want:

select o.*,
       (select ros.tbl_status_order_id
        from tbl_rel_order_status ros
        where ros.tbl_order_id = o.id
        order by ros.datetime desc
        limit 1
       ) as last_status
from tbl_order o
having last_status in ('6', '7', '8', '11', '13', '15');

Upvotes: 1

Related Questions