Query return does not hit

0

I'm trying to create a query that returns the calculated cost of certain products, and I'm curious to know why when we do joins - in this case a left join - the result that should be x becomes w .

The query looks something like this:

select * from
(
select

mbr_1.name 'Sabor',
mbr_2.name 'Tipo_Produto',
mbr_3.name 'Produtos',
mbr_4.name 'null',
vps.period_name 'periodo',
sum(mba_dest.calculated_cost) 'custo'

from mdl_member_instances mbi

--origem
left join mdl_assignments mba_org on mba_org.source_mbi_id = mbi.id

--membros
left join mdl_members mbr_1 on mbr_1.id = mbi.member_id
left join mdl_members mbr_2 on mbr_2.id = mbr_1.parent_id
left join mdl_members mbr_3 on mbr_3.id = mbr_2.parent_id
left join mdl_members mbr_4 on mbr_3.id = mbr_3.parent_id


--destino
left join mdl_assignments mba_dest on mba_dest.destination_mbi_id = mbi.id

--periodo
left join view_period_scenarios vps on vps.id = mbi.period_scenario_id

--filtros do select
where mbr_3.module_type = 2

group by 
mbr_1.name,
mbr_2.name,
mbr_3.name,
mbr_4.name,
vps.period_name,
mba_dest.calculated_cost

union all

SELECT

MBR_1.[name]  'MBR_1',
MBR_2.[name]  'MBR_2',
MBR_3.[name]  'MBR_3',
MBR_SOURCE.[name]  'MBR_SOURCE',
VPS.[period_name]  'PERIOD',
sum(MBA_SOURCE.[calculated_cost]) AS 'COST'

FROM MDL_MEMBER_INSTANCES MBI_ORG

--ORIGEM
LEFT JOIN
MDL_ASSIGNMENTS MBA_SOURCE ON MBA_SOURCE.[source_mbi_id] = MBI_ORG.[id]

--DESTINO
LEFT JOIN
MDL_ASSIGNMENTS MBA_DESTINATION ON MBA_DESTINATION.[destination_mbi_id] = MBI_ORG.[id]

--PERÍODO-CENARIO
LEFT JOIN VIEW_PERIOD_SCENARIOS VPS ON VPS.[id] = MBI_ORG.[period_scenario_id]

--MEMBROS
LEFT JOIN MDL_MEMBERS MBR_SOURCE ON MBR_SOURCE.[id] = MBI_ORG.[member_id]
LEFT JOIN MDL_MEMBERS MBR_1 ON MBR_1.[id] = MBR_SOURCE.[parent_id]
LEFT JOIN MDL_MEMBERS MBR_2 ON MBR_2.[id] = MBR_1.[parent_id]
LEFT JOIN MDL_MEMBERS MBR_3 ON MBR_3.[id] = MBR_2.[parent_id]

--FILTROS
WHERE MBR_SOURCE.[module_type] = 0 AND MBR_SOURCE.[type] = 1

group by 

MBR_1.[name],
MBR_2.[name],
MBR_3.[name],
MBR_SOURCE.[name],
VPS.[period_name],
MBA_SOURCE.[calculated_cost]

) mdl_members
    
asked by anonymous 24.02.2015 / 19:54

0 answers