CODEWITHSUNDEEP
mysqlselectleft-join
Architect - Hitesh
Architect - Hitesh

Reputation: 1239

Select customer who don't have data on sub tables

I am having customer data table is as below:

enter image description here

And also sub data table is as below:

enter image description here

Now i m trying out to access the data which is not available in sub data table

For example

I want customer as per below conditions

1) today's date should be within the customer expiry date minus(-) 2 months which means access customer which is gonna expire in two months from today date

2) customer should be a member and not a admin

3) today system haven't sent a notification to particular customer

4) Notification type should be "6"

So as per above test data

if today date is 2018-01-09 then result should be

First Customer "Jiren" system already sent notification

First Customer "Lee" system already sent notification

So I want customer id 3 and 4 in result

I have used following query but it is not working properly: 

SELECT customer_id,customer_token,customer_name,expiry_date
,DATE_FORMAT(`expiry_date` - INTERVAL '2' MONTH, '%Y-%m-%d') AS 
ExpireLim,notification_id FROM customer
LEFT JOIN notification ON customer.customer_id = notification.no_customer_id 
WHERE customer_token != '' AND no_type = '6' 
AND DATE_FORMAT(no_date,'%Y-%m-%d') = '2018-01-09' 
AND customer_token != 'device_token' AND is_member = 1 
AND CURRENT_DATE = `expiry_date` - INTERVAL '2' MONTH 
AND is_admin = 0

Upvotes: 0

Views: 40

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Don't join. If you want to know whether a notification exists, use an EXISTS or IN clause.

SELECT 
  customer_id,
  customer_token,
  customer_name,
  expiry_date,
  DATE_FORMAT(EXPIRY_DATE - INTERVAL 2 MONTH, '%Y-%m-%d') as expirelim
FROM customer c
WHERE customer_token != ''
AND expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 2 MONTH 
AND is_member = 1
AND is_admin = 0
AND NOT EXISTS
(
  SELECT *
  FROM notification n
  WHERE n.customer_id = c.customer_id
  AND n.no_type = 6
  AND date(n.no_date) = date '2018-01-09'
);

I suppose that notification.customer_id cannot be null. So you can use the simpler NOT IN instead of NOT EXISTS:

AND customer_id NOT IN
(
  SELECT customer_id
  FROM notification
  WHERE no_type = 6
  AND date(no_date) = date '2018-01-09'
);

You may have to adjust the expiry clause. I took it to mean what expires from now to now + 2 months. This includes the time. Maybe you'd rather want date(expiry_date). Month lengths are not a precise thing by the way; from February 1 to March 1 is a shorter time than from March 1 to April 1. Maybe you want to make this rule 60 days or the like instead.

Upvotes: 1

Related Questions