-1

I have two table

  1. laces_users_profile
  2. laces_order

laces_order have order list of all user. laces_users_profile have all customer data.

laces_users_profile have unique key laces_user_id that is foreign key in laces_order.

My Tables look like this

laces_orders

enter image description here

laces_users_profile

enter image description here

I want to retrieve all customers, Who have not order from last 30 Days.

Meet Patel
  • 105
  • 8
  • Refer https://stackoverflow.com/questions/2041575/mysql-query-records-between-today-and-last-30-days, you need not between – Devsi Odedra Dec 09 '19 at 10:34
  • Most people here want sample table data and expected result as formatted text, not as images. (And I can't read that tiny image text...) – jarlh Dec 09 '19 at 10:36
  • You should consider using a more consistent naming convention across your database. For example, if the table is called `laces_users_profile`, the order should have a `laces_user_profile_id` or something rather than `customer_id`. Or you should call the `laces_users_profile` table `customers`. – BadHorsie Dec 09 '19 at 10:42
  • Does this answer your question? [MySQL Query - Records between Today and Last 30 Days](https://stackoverflow.com/questions/2041575/mysql-query-records-between-today-and-last-30-days) – Larry Tang Dec 09 '19 at 11:13
  • What if I add customer today? I will also considered as customer not given order in last 30 day. It should not happen. Recently added customer should not come in list of those customer who did not given order since last 30 days. how achieve that condition. @BadHorsie – Meet Patel Dec 11 '19 at 07:29
  • What if I add customer today? I will also considered as customer not given order in last 30 day. It should not happen. Recently added customer should not come in list of those customer who did not given order since last 30 days. how achieve that condition. @LarryTang – Meet Patel Dec 11 '19 at 07:30

1 Answers1

1

You could use a left join antipattern:

select u.*
from laces_users_profiles u
left join laces_order o
    on o.customer_id = u.laces_user_id 
    and o.create_date > now() - interval 30 day
where o.customer_id is null

The same result can be achieved with a not exists condition and a correlated subquery:

select u.*
from laces_users_profiles u
where not exists (
    select 1
    from join laces_order o
    where 
        o.customer_id = u.laces_user_id 
        and o.create_date > now() - interval 30 day

)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thanks !! @GMB Its Working !! – Meet Patel Dec 09 '19 at 10:40
  • What if I add customer today? I will also considered as customer not given order in last 30 day. It should not happen. Recently added customer should not come in list of those customer who did not given order since last 30 days. how achieve that condition. @GMB – Meet Patel Dec 11 '19 at 07:29