CODEWITHSUNDEEP
mysqlsqldatejoinselect
Giorgos Cut
Giorgos Cut

Reputation: 67

mySQL joins query with dates

hey so I have these tables and data: 

create table CUSTOMER(CustNum INT, CustTell INT, CustFname VARCHAR(15), CustLname VARCHAR(15), CustCounty VARCHAR(20),
CustTown VARCHAR(20), CustStreet VARCHAR(20), CustPostCode CHAR(7), CustDob DATE, CustEmail VARCHAR(45), Branch VARCHAR(25), PRIMARY KEY(CustNum, CustTell));

create table PURCHASE(TDate DATE, TTime TIME, BramchAddr VARCHAR(25), TillNo INT, CustNo INT, AssistantNo INT, ProdCode INT, Qty INT, BranchAddr VARCHAR(25), PRIMARY KEY(TDate, TTime));

INSERT INTO CUSTOMER (CustNum, CustTell, CustFname, CustLname, CustCounty, CustTown, CustStreet, CustPostcode, CustDob, CustEmail, Branch) VALUES 
(436196, 0763126847, "John", "Murphy", "West Yorkshire", "Leeds", "Calverley Str" ,"LS1 1AZ", '2000-03-24', "[email protected]", "London"), 
(538912, 0723749154, "Isla", "Taylor", "Leicestershire", "Leicester", "Nedham Str" ,"LE1 1AF", '1996-11-02', "[email protected]", "Brighton"),
(368549, 0796871325, "Charlie", "Byrne", "Greater London", "London", "Peel Str" ,"E1 6AN", '1976-12-17', NULL, "Southampton"),
(377461, 0731895518, "William", "Evans", "Telford and Wrekin", "Telford", "Peel Str" ,"TF1 1AF", '1978-08-21', "[email protected]", "Surrey"),
(652387, 0778234657, "Thomas", "Li", "Merseyside", "Liverpool", "Dumbrees Str" ,"L1 0AH", '1999-01-00', "[email protected]", "Lancaster"),
(543234, 0723432133, "Alex", "Michael", "West Yorkshire", "Leeds", "Richmond Green Str", "LS1 1HE", '1997-06-28', "[email protected]", "London");


INSERT INTO PURCHASE (TillNo, TDate, TTime, CustNo, AssistantNo, ProdCode, Qty, BranchAddr) VALUES
(005, '2020-03-10', '19:12:55', 368549, 48964, 3645789, 3, "Southampton"),
(005, '2020-03-10', '19:32:23', 368549, 48964, 6875436, 1, "Southampton"),
(005, '2020-03-10', '19:42:55', 368549, 48964, 8681466, 1, "Southampton"),
(005, '2020-03-11', '15:48:05', 652387, 48964, 3629191, 2, "Southampton"),
(006, '2020-03-10', '11:43:23', 436196, 18537, 3645789, 3, "Southampton"),
(004, '2020-03-10', '11:49:34', 436196, 56485, 2528726, 1, "Southampton"),
(001, '2020-02-25', '14:22:48', 377461, 23485, 6875436, 2, "Southampton"),
(001, '2020-02-25', '14:52:48', 377461, 23485, 1651687, 2, "London"),
(001, '2020-01-15', '18:18:56', 538912, 23485, 1651687, 1, "London"),
(006, '2020-01-15', '18:45:56', 538912, 18537, 8658458, 1, "London"),
(002, '2020-01-12', '09:32:14', 652387, 15615, 3697260, 3, "London"),
(003, '2019-05-26', '11:20:43', 368549, 83157, 4494292, 8, "Lancaster"),
(001, '2021-03-16', '13:43:40', 368549, 23485, 3697260, 6, "Southampton"),
(005, '2019-05-19', '17:40:16', 538912, 48964, 8005597, 5,"London"),
(005, '2019-11-27', '01:48:18', 538912, 48964, 5600196, 1, "Brighton"),
(006, '2020-12-06', '18:21:36', 538912, 18537, 6889045, 2, "Lancaster"),
(003, '2020-03-10', '08:09:05', 377461, 83157, 1076404, 7, "Lancaster"),
(005, '2020-07-24', '14:58:47', 538912, 48964, 4494292, 2, "Lancaster"),
(004, '2020-10-17', '19:19:32', 368549, 56485, 1651687, 2, "Southampton"),
(005, '2019-05-20', '22:19:57', 652387, 48964, 8681466, 6, "Southampton"),
(006, '2020-10-12', '00:50:32', 368549, 18537, 9346249, 7, "London"),
(001, '2020-11-04', '22:08:54', 377461, 23485, 8681466, 6, "Brighton");

And i want to make a query where it shows which customer accounts have not been active for the last month. This is what i've tried but failed: 

SELECT PURCHASE.CustNo, PURCHASE.TDate, CUSTOMER.CustNum FROM (CUSTOMER
LEFT JOIN PURCHASE ON CUSTOMER.CustNum = PURCHASE.CustNo)
WHERE PURCHASE.TDate = CURDATE() - INTERVAL 1 MONTH
ORDER BY PURCHASE.CustNo;

Assuming I can change the dates, is there any way to make such a query?

The desired resault would be all of the customers details who have not been active for the past month.

Upvotes: 2

Views: 34

Answers (1)

GMB
GMB

Reputation: 222432

I want to make a query where it shows which customer accounts have not been active for the last month.

I would recommend not exists to exhibit customers that did not had a purchase between now and one month ago:

select c.*
from customer c
where not exists (
    select 1 
    from purchase p 
    where p.custno = c.custnum and p.tdate >= current_date - interval 1 month
)

This query would benefit an index on purchase(custno, tdate).

Upvotes: 1

Related Questions