Filter Customers Table and Transaction Table to find out which customer who never did any transactio-CodePudding

I have 2 table

Transaction (with typeof deposit and withdrawal)
Customers

var get30dayReport = `
SELECT t1.*,
       t2.*
FROM TRANSACTION t1
LEFT JOIN customers t2 ON t1.customer = t2.username
WHERE TYPE = 'deposit'
  AND date > NOW() - INTERVAL 30 DAY
  AND date < NOW()   INTERVAL 30 DAY
ORDER BY `t1`.`date` DESC`

this is the query my friend give me before , but the report not giving the result what I wish

I wish to get a report that which customer who never did a new transaction within 1 month and their last deposit date , and their amount which is stored inside transaction table.

CodePudding user response：

You can't get the result only by adding JOIN and usual date conditions because you want the condition about NOT HAVING some records so you need to use a condition with a subquery (SELECT COUNT(*) ...)=0 or NOT EXISTS (SELECT 1 ...):

SELECT t1.*,
       t2.*
FROM TRANSACTION t1
LEFT JOIN customers t2 ON t1.customer = t2.username
WHERE t1.TYPE = 'deposit'
  AND (SELECT COUNT(*) from TRANSACTION t3
       where t3.customer = t2.username
       AND t3.TYPE = 'deposit'
       AND t3.date > NOW() - INTERVAL 30 DAY)=0
  AND t1.date=(SELECT MAX(t4.date) from TRANSACTION t4
       where t4.customer = t2.username
         AND t4.TYPE = 'deposit')