mysql get all cards record that have last 7 failed transactions

Question

i have three table cards (id,...) order_cards (card_id, order_id....) transactions (order_card_id, status, ....)

i want to get all cards that had last 7 transactions failed

i am trying something like that

select  cards.*,
(
select sum(case tmp1.status when 'fail' then 1 else 0 end) from  
(select transactions.status from order_cards
left join transactions on transactions.order_card_id = order_cards.id
where order_cards.card_id = cards.id
order by transactions.id desc limit 7
) as tmp1
) as total_fail
from cards

group by cards.id
having total_fail > 5

getting this error

 Unknown column 'cards.id' in 'where clause'

issue with above query is parent id not working in level 2 subquery. tried to use having clause with count but its not working with limit any suggestion thanks

already tried

Use column of parent query in subquery of a subquery

MYSQL - Get all records that have more than 1 record for the same id

Can you describe the exact error you are getting? – artemis Mar 09 '19 at 00:17 — artemis, Mar 09 '19 at 00:17
@JerryM. i have updated query can you take a look now – Zeeshan Anjum Mar 09 '19 at 00:24 — Zeeshan Anjum, Mar 09 '19 at 00:24

Dinesh Arora · Answer 1 · 2019-03-09T01:00:19.780

0

This should work. Select c.*, SUM(t.status = 'FAILED') AS failed_txns from cards c, order_cards oc, transactions t where t.status='FAILED' and oc.order_id = t.order_card_id and c.id = oc.card_id group by c.id having failed_txns >= 7

edited Mar 09 '19 at 01:00

answered Mar 09 '19 at 00:53

Dinesh Arora

2,115
3
24
30

this is not working is not working in my case. i need to check last 7 transaction not 7 failed transactions – Zeeshan Anjum Mar 09 '19 at 15:09
You mean to say that you need to check only the last 7 transactions and if all 7 txnx failed are of same card id they need to be displayed? Ex - There are 3 types of cards A, B and C and there were 16 txns in total. Of the last 7 txns, if A failed 2 times, B failed 3 times and C failed 2 times (all 7 txns failed but for different cards), what do you intend to see in out put A = 2, B =3 and C =2 or do you want to see NONE as neither A, B or C failed 7 consecutive times. – Dinesh Arora Mar 10 '19 at 04:32
yes i have to check last 7 transaction of card A. if all failed then it will be listed. same for Card B and C – Zeeshan Anjum Mar 10 '19 at 16:48

jxc · Accepted Answer · 2019-03-10T07:35:33.037

I think the parent-ids can NOT be used in the derived table in FROM clause, but are still accessible in the WHERE clause and SELECT list in the Sub-Sub-query. So in your case, you can find the 8th latest transaction.id (LIMIT 7,1) of the card_id and then SUM() based on that transaction.id: (note when there are less than 8 transactions in the same card_id, LIMIT 7,1 will return NULL, in such case, all transactions should be counted, and thus below: t1.id > IFNULL((...), 0)):

SELECT  c.*
,   IFNULL((
      SELECT sum(t1.status = 'fail') 
      FROM order_cards o1
      JOIN transactions t1 on t1.order_card_id = o1.id
      WHERE o1.card_id = c.id
      AND t1.id > IFNULL((
          SELECT t2.id 
          FROM order_cards o2
          JOIN transactions t2 on t2.order_card_id = o2.id
          WHERE o2.card_id = c.id
          ORDER BY t2.id DESC 
          LIMIT 7,1
      ), 0)
    ), 0) as total_fail
FROM cards c
HAVING total_fail > 5

I tested a similar SQL on the database of my own server which works fine, and the same method might apply to your case.

i need to count total fail from last 7 transactions. above query will count fail from all transactions — Zeeshan Anjum, Mar 09 '19 at 22:07
I modified the SQL so it runs SUM() based on the transaction_id calculated by a sub-query. let me know if it works. — jxc, Mar 10 '19 at 06:43

mysql get all cards record that have last 7 failed transactions

2 Answers2