โจทย์ครับ : The number of motorcycles following each matched todouhuken (if it is null, it will be showed 0).
Query ของผมครับ
select t.todouhuken_code,
t.todouhuken_name,
t.todouhuken_area_code2,
area_name,
count(case when motorcycle_no is null then 0 else motorcycle_no end) as cnt
from mst_area a left join
mst_todouhuken t
on (a.area_code2 = t.todouhuken_area_code2) and (a.area_code = t.todouhuken_area_code) left join
search_motorcycle s
on t.todouhuken_code = s.dealer_todouhuken_code left join
mst_model_v2 v2
on (s.motorcycle_model_code = v2.model_code) and (s.motorcycle_maker_code = v2.model_maker_code)
where motorcycle_jyoukyo in (1,5,6) and
model_maker_code = 4 and
dealer_todouhuken_code > 0
group by todouhuken_code,todouhuken_area_code2,todouhuken_name,todouhuken_name,area_name asc
order by todouhuken_area_code2,todouhuken_code asc
1. Your result don't have cnt = 0 because of your query can't count when todouhuken_code has cnt = 0
2. With motorcycle_maker_code = 4, only have todouhuken_code=1,3,4,....
Using this query to check:
----------------
SELECT
*
FROM
webikept.search_motorcycle
WHERE
motorcycle_maker_code = 4
GROUP BY dealer_todouhuken_code;
-----------------
So if you use t.todouhuken_code = s.dealer_todouhuken_code
AND model_maker_code = 4 in WHERE, it will make data lost
Load balance : Server 01
