Using Case in a select

[whitsey]

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

[fenway]

Why not a LEFT JOIN, then?