Bardworx Bardworx - 6 months ago 20
MySQL Question

MySQL: get distinct, different, values between two queries

I have a query with a sub-query. Both return a list of users IDs. in inner query gives me all customers who used cash in a particular location within a given time frame.

The second, selects all user ids from the first that ordered after a specific date and did not use cash as a form of payment.

select distinct c.user_id
from (
select distinct o.user_id
from `order` as o
inner join payments as p on p.id = o.id
where o.orderplaced_ts > "2016-01-01 00:00:00"
and o.store_id = "12"
and p.payment_method = "Cash"
) as c
inner join `order` as o on c.user_id = o.user_id
inner join `payments` as p on o.id = p.id
where o.orderplaced_ts > "2016-03-13 00:00:00"
and o.store_id = "12"
and p.payment_method != "Cash"


How do I get the cross between the users that did no order since
2016-03-13
.

For reference:

select distinct o.user_id
from `order` as o
inner join payments as p on p.id = o.id
where o.orderplaced_ts > "2016-01-01 00:00:00"
and o.store_id = "12"
and p.payment_method = "Cash"


Returns 236 Unique IDs - call this set A

Both queries combines return 160 unique ID - call this set B

Which IDs are in Set A and are
not
in set B

Answer

I would prefer to get some sqlfiddle from your side.

But since I have not, here is my attempt:

select distinct c.user_id
from (
    select distinct o.user_id
    from `order` as o
    inner join payments as p on p.id = o.id
    where o.orderplaced_ts > "2016-01-01 00:00:00"
    and o.store_id = "12"
    and p.payment_method = "Cash"
) as c
LEFT JOIN `order` as o 
ON c.user_id = o.user_id
   AND o.orderplaced_ts > "2016-03-13 00:00:00"
   AND o.store_id = "12"
LEFT JOIN `payments` as p 
ON o.id = p.id
   AND p.payment_method != "Cash"
WHERE p.id IS NULL
Comments