I am trying to get a customer who has cancelled all orders , I am not able to do so

SELECT
   oc.customer_id
  ,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name
  ,oc.customer_email
  ,oc.customer_phone
  ,a.country
FROM online_customer oc
JOIN address a ON oc.address_id = a.address_id
WHERE oc.customer_id = (
  SELECT
    oh.customer_id
 FROM order_header oh
 WHERE oh.order_status = 'Cancelled'
)
GROUP BY oc.customer_id, concat(oc.customer_fname,' ', oc.customer_lname), oc.customer_email, oc.customer_phone, a.country;

There is a column in order_header table called Order_status which has values "Shipped" ,"In process" and "Cancelled". Now I want a customer_id who has all orders as "Cancelled" in order_status. I am not able to get the logic right

>Solution :

You can have this logic by grouping the orders table by the customer_id then checking it has only one distinct order_status, and thats the single status (using max/min) is ‘Canceled’

select oc.customer_id,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name,oc.customer_email,oc.customer_phone,
a.country
from online_customer oc join address a on oc.address_id = a.address_id
where oc.customer_id=(select oh.customer_id
from order_header oh 
group by customer_id 
having count(distinct order_status) = 1 and max(order_status) = 'Cancelled')
group by oc.customer_id,concat(oc.customer_fname,' ',oc.customer_lname),oc.customer_email,oc.customer_phone,a.country;