Double filtering.

[Jacky Luk]

select 'Normal' AS type0, count(c.CustomerCode) AS cnt, c1.OrderCode, IFNULL(sum(c1.NetSales),0) AS NetSales, date(c1.CustomerCreationDate, 'unixepoch', 'localtime')
 from customer c LEFT JOIN 
                (select c.*, o.*, od.* from customer c LEFT JOIN orders o ON (c.CustomerCode = o.CustomerCode)
          LEFT JOIN order_details od ON (o.OrderCode = od.OrderCode)

    group by c.CustomerCode
      having NetSales < 5000)  c1
           where strftime('%Y-%m', date(c1.CustomerCreationDate, 'unixepoch', 'localtime'))
                = strftime('%Y-%m', date('now', '-0 MONTH'))
               

I tried to filter the normal customer 1st, then the customer created in this month
The first subquery returns no sets.
I seem to be having trouble when the order details table has got nothing in it

update:

select c.*, o.*, IFNULL(sum(od.NetSales),0) AS NetSales from customer c LEFT JOIN orders o ON (c.CustomerCode = o.CustomerCode)
          LEFT JOIN order_details od ON (o.OrderCode = od.OrderCode)

    group by c.CustomerCode
      having NetSales < 5000

By further breakdown of the problem, how can this return something
when order details has nothing inside, thus a 0 NetSales

update2:
I solved it by adding another union, making the no sales customer in the normal's set
, while having the VIP unchanged


Thanks
Jack