Join two table with specific conditions in mysql

I have two table

Table 1: (user) id,name,username,password

table 2: (order) id,user,status

status column have two value: 1(cancel order) or 2(success order)

I want show all user list with order count as orders (order count for only success order)

I write this sql :

  select `user`.* , count(`sale`.`user`) as 'orders'
  from `user` 
  left join `sale`
      on `sale`.`user` = `user`.`id`
  where `user`.`type` = 'customer'
    and (`sale`.`status` IS NULL OR `sale`.`status` = 2)
  group by `user`.`id`
  order by `user`.`id` DESC

in this query if user not have any order or have success order is showing in result

but if user have canceled order is not show in result

I want show all user in result with orders column

If order status is 2, order column show count of success order

If order status is 1 or null (user not have any order) in order column of result show 0 count

Thanks in advance

Answer

Move the condition on status to the on clause:

  select u.* , count(s.user) as orders
  from `user` u left join
       sale s
        on s.`user` = u.id AND s.status = 2
  where u.`type` = 'customer'
  group by u.id  -- ok, assuming id is unique
  order by u.id  DESC

Advertisement

Answer