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