Help with MySQL Query using CASE statement
- by hairdresser-101
I am trying to group a number of customers together based on their "Head Office" or "Parent" location.
THis works ok except for a flaw which I didn't forsee when I was developing my system... For customers that did not have a "Parent" (standalone business) I defaulted the parent_id to 0. Therefore, my data would look like this:
id parent_id customer
1 0 CustName#1
2 4 CustName#2 - Melbourne
3 4 CustName#2 - Sydney
4 0 CustName#2 (Head Office)
What I want to do is Group my results together so that I have one row for CustName#1 and one row for CustName#2 BUT my problem is that there is no parent record for parent_id=0 and these rows are being excluded when using an inner join.
I've tried using a case statement but that is not working either (parents are still being ignored)
Any help would be greatly appreciated.
Here is my query (My CASE is basically trying to get the business_name from the customer table based on the parent_id EXCEPT when the parent_id = 0, THEN just use the customer_name that is listed in the job_summary table):
SELECT
js.month_of_year,
(CASE js.parent_id WHEN 0 THEN js.customer_name ELSE c.business_name END) as customer,
SUM(js.jobs),
SUM(js.total_cost),
sum(js.total_sell)
FROM JOB_SUMMARY js INNER JOIN
customer c on js.parent_id=c.id
group by
js.month_of_year,
(CASE c.parent_id WHEN 0 THEN js.customer_name ELSE c.business_name END) ORDER BY `customer` ASC
