0

enter image description here

I am trying to get data that customer who didnt have any new transaction with type deposit within 30 days

Eg: customer MS00001 wont be query because it still have new record with deposit within 30 day, the result should only show me MS00002 because it didnt have and type of deposit within 30 days

enter image description here

meanwhile I have a customer table which have the customer details.

so how could I use 1 query to solve all of this

target output : transaction table + customer table who didnt have new record within 30 days

below is the query that I trying to get customer who didnt have any new record in transaction table.

SELECT t1.* 
    FROM transaction t1
LEFT JOIN (
    SELECT customer FROM transaction 
    WHERE date <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
) t2 ON t2customer = t1.customer
WHERE t1.date <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
AND t1.type = 'deposit'
AND t2.customer  IS NULL
ORDER BY trans_id DESC
Hong Ernest
  • 67
  • 1
  • 10

1 Answers1

1

Answer depends a bit what you want see from transaction table. The simpliest way to do this with one query is to group transactions by customer and then select customers whose last deposit was made over 30 days ago. In this way you are able to see their last transactions details in select

SELECT transaction.*, customer.*
FROM transaction
JOIN customer
    ON customer.id = transaction.customer_id
WHERE transaction.type = "deposit"
GROUP BY customer.id
HAVING MAX(transaction.date) <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
TCFDS
  • 584
  • 4
  • 16
  • Static analysis: 1 errors were found during analysis. Unrecognized statement type. (near "SELECT transaction" at position 0) SQL query: Documentation SELECT transaction.*, customer.* FROM transaction JOIN customer ON customer.id = transaction.customer_id WHERE transaction.type = "deposit" GROUP BY customer.id HAVING MAX(transaction.date) <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 – Hong Ernest Jan 21 '22 at 18:56
  • Final closure was missing, not sure if it could have been just that – TCFDS Jan 21 '22 at 19:10
  • ```SELECT * FROM transaction,customers JOIN customers ON customers.username= transaction.customer WHERE transaction.type = "deposit" GROUP BY customers.username HAVING MAX(transaction.date) <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY``` #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 25' at line 7 – Hong Ernest Jan 21 '22 at 19:10
  • I modified something but the error show: SQL query: Documentation SELECT transaction.*, customers.* FROM transaction JOIN customers ON customers.username = transaction.customer WHERE transaction.type = "deposit" GROUP BY customers.username HAVING MAX(transaction.date) <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY) MySQL said: Documentation #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'my77admin.transaction.trans_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Hong Ernest Jan 21 '22 at 19:12
  • Seems that in your configuration, you are not able to show fields from transaction table in select if you don't use aggregation related to that... But you could try to do `GROUP BY customer.id, transaction.customer_id` and then aggregation would be set in both tables – TCFDS Jan 21 '22 at 19:20
  • may I know how to use aggregation related to this? Still on learning progress – Hong Ernest Jan 21 '22 at 19:58
  • If `GROUP BY`modification didn't help, you select field in `transaction` table with aggregation function. E.g. `MAX(transaction.amount)` OR `GROUP_CONCAT(transaction.products SEPARATOR " ")`. But I think you should first try to modify `GROUP BY`clause. `GROUP BY transaction.customer_id` could also work – TCFDS Jan 21 '22 at 20:06