Bardworx Bardworx - 2 months ago 10
MySQL Question

MYSQL: Only retrieve users with specific data field

I have three tables:

Order
,
Payment
, and
User
.

I'm trying to find only the users who used a particular payment method (Cash) and who did not use any other form of payment.

The simple query I built looks like this:

select
user.id,
user.fname,
user.lname,
user.email
from `order` as o
inner join payments on o.id = payments.order_id
inner join user on o.user_id = user.id
where o.orderplaced_ts > "2016-08-01 00:00:00"
and o.store_id = 6
and o.order_status != "Cancelled"
and payments.payment_method = "Cash"
group by user.id


However, this will return users if they used Cash as a payment method at least once, during the time frame. How do I limit to ONLY cash and no other forms of payment?

Should I have multiple subqueries for each form of payment then compare each return vs other to eliminate more and more users?

Answer

You can use a not in user.id that use other methods

  select 
  user.id,
  user.fname,
  user.lname,
  user.email
  from `order` as o
  inner join payments on o.id = payments.order_id
  inner join user on o.user_id = user.id
  where o.orderplaced_ts > "2016-08-01 00:00:00"
  and o.store_id = 6
  and o.order_status != "Cancelled"
  and payments.payment_method = "Cash"
  and user.id not in (select distinct o.user_id 
                      from `order`  
                      inner join  payments on o.id = payments.order_id 
                      where payments.payment_method <> "Cash" )
  group by user.id