with
man_total as (select * from DRINK_HABITS where CATEGORY_CODE = 110 and GENDER_CODE = 2),
woman_total as (select * from DRINK_HABITS where CATEGORY_CODE = 110 and GENDER_CODE = 3),
man_drink as (select * from DRINK_HABITS where CATEGORY_CODE = 120 and GENDER_CODE = 2),
woman_drink as (select * from DRINK_HABITS where CATEGORY_CODE = 120 and GENDER_CODE = 3)

select
PREFECTURE.PF_CODE as CODE,
PREFECTURE.PF_NAME as NAME,
man_total.AMT
from
PREFECTURE
-- join DRINK_HABITS
-- on PREFECTURE.PF_CODE = DRINK_HABITS.PF_CODE
join man_total on man_total.PF_CODE = PREFECTURE.PF_CODE
join woman_total on woman_total.PF_CODE = PREFECTURE.PF_CODE
join man_drink on man_drink.PF_CODE = PREFECTURE.PF_CODE
join woman_drink on woman_drink.PF_CODE = PREFECTURE.PF_CODE
-- where GENDER_CODE in (2,3)
-- and CATEGORY_CODE = 120
-- group by PREFECTURE.PF_CODE