I have 2 select statements that return results in the same structure summarized by month and year and equipment, based on different filters.

I then do a left outer join on my master equipment table to return the filter result for that piece of equipment for that month and year, however since my equipment table doesn't have month and year how to I ensure to match my results as I tried with the below query but it will drop a record since I'm doing a left outer join.

ONMARC QUERY:

SELECT SUM(Cost) TCost, Month, Year, EquipmentID
FROM Marc_Cost 
WHERE Year = 2024 AND Month <= 2 AND Cost_Element = '0000555090' 
GROUP BY Cost_Element, Month, Year, EquipmentID
ORDER BY Year, Month

OFFMARC QUERY:

SELECT SUM(Cost) TCost, Month, Year, EquipmentID
FROM Marc_Cost
WHERE Year = 2024 AND Month <= 2 AND Cost_Element <> '0000555090' 
GROUP BY Month, Year, EquipmentID

Equipment Table and my expected result is show with the result set of MARC and ON MARC in the Excel sheet provided in the link.

The query I have thus far that omits records

SELECT A.Equipment_Numnber, A.EquipmentDesc, iFnull(a.ONMARC,0) ONMARC, ifnull(C.TCost,0) offmarc, a.Month Month, A.Year Year
FROM (
    SELECT EQ.*, IFNULL(B.TCost,0) ONMARC, IFNULL(B.Month,0) Month, IFNULL(B.Year,0) Year
    FROM (
        SELECT Equipment_Numnber,EquipmentDesc
        FROM v_marc_equipment
    ) EQ    
    LEFT OUTER JOIN (
        SELECT SUM(Cost) TCost, Month, Year, EquipmentID
        FROM Marc_Cost 
        WHERE Year = 2024 AND Month <= 2 AND Cost_Element = '0000555090' 
        GROUP BY Cost_Element,Month, Year, EquipmentID
        ORDER BY Year, Month
    ) B
    ON EQ.Equipment_Numnber = RIGHT(B.EquipmentID,9)
) A
LEFT OUTER JOIN  (
    SELECT SUM(Cost) TCost, Month, Year, EquipmentID
    FROM Marc_Cost
    WHERE Year = 2024 AND Month <= 2 AND Cost_Element <> '0000555090' 
    GROUP BY Month, Year, EquipmentID
) C
ON a.Equipment_Numnber = RIGHT(C.EquipmentID,9)
AND A.Month = C.Month AND A.Year = c.Year   
ORDER BY A.yEAR, a.Month

How do I need to change this query to get both results to not omit results?

!Ah8iSJABBgebgo1TwC0z9yWMmMp3gA?e=c7Hv6C

I have 2 select statements that return results in the same structure summarized by month and year and equipment, based on different filters.

I then do a left outer join on my master equipment table to return the filter result for that piece of equipment for that month and year, however since my equipment table doesn't have month and year how to I ensure to match my results as I tried with the below query but it will drop a record since I'm doing a left outer join.

ONMARC QUERY:

SELECT SUM(Cost) TCost, Month, Year, EquipmentID
FROM Marc_Cost 
WHERE Year = 2024 AND Month <= 2 AND Cost_Element = '0000555090' 
GROUP BY Cost_Element, Month, Year, EquipmentID
ORDER BY Year, Month

OFFMARC QUERY:

SELECT SUM(Cost) TCost, Month, Year, EquipmentID
FROM Marc_Cost
WHERE Year = 2024 AND Month <= 2 AND Cost_Element <> '0000555090' 
GROUP BY Month, Year, EquipmentID

Equipment Table and my expected result is show with the result set of MARC and ON MARC in the Excel sheet provided in the link.

The query I have thus far that omits records

SELECT A.Equipment_Numnber, A.EquipmentDesc, iFnull(a.ONMARC,0) ONMARC, ifnull(C.TCost,0) offmarc, a.Month Month, A.Year Year
FROM (
    SELECT EQ.*, IFNULL(B.TCost,0) ONMARC, IFNULL(B.Month,0) Month, IFNULL(B.Year,0) Year
    FROM (
        SELECT Equipment_Numnber,EquipmentDesc
        FROM v_marc_equipment
    ) EQ    
    LEFT OUTER JOIN (
        SELECT SUM(Cost) TCost, Month, Year, EquipmentID
        FROM Marc_Cost 
        WHERE Year = 2024 AND Month <= 2 AND Cost_Element = '0000555090' 
        GROUP BY Cost_Element,Month, Year, EquipmentID
        ORDER BY Year, Month
    ) B
    ON EQ.Equipment_Numnber = RIGHT(B.EquipmentID,9)
) A
LEFT OUTER JOIN  (
    SELECT SUM(Cost) TCost, Month, Year, EquipmentID
    FROM Marc_Cost
    WHERE Year = 2024 AND Month <= 2 AND Cost_Element <> '0000555090' 
    GROUP BY Month, Year, EquipmentID
) C
ON a.Equipment_Numnber = RIGHT(C.EquipmentID,9)
AND A.Month = C.Month AND A.Year = c.Year   
ORDER BY A.yEAR, a.Month

How do I need to change this query to get both results to not omit results?

https://1drv.ms/x/s!Ah8iSJABBgebgo1TwC0z9yWMmMp3gA?e=c7Hv6C

• sql
• databricks

• 100008356 HT-153, TRUCK-HAUL 795F AC #N/A -263871.14 2 2024 , this is the row missing from my original sql query, and it has to do that this record do not exist when i do the left outer join and this will drop this row as its coming from the right table, – user1546143 Commented Jan 31 at 18:08

1 Answer 1

I changed the left outer join to a UNION and then left outer join to equipment.

SELECT B.*, EQ.EquipmentDesc

FROM
(SELECT SUM(a.ONMARC_Cost) ONMARC_Cost, SUM(a.OFFMARC_COST) OFFMARC_COST, a.Month Month, a.Year Year, a.EquipmentID

FROM
(Select SUM(Cost) ONMARC_Cost, 0 OFFMARC_COST, Month, Year,EquipmentID  from Marc_Cost 
Where Year >= 2024 and  Cost_Element = '0000555090' 
GROUP BY Cost_Element,Month, Year,EquipmentID


UNION

Select 0 ONMARC_Cost, SUM(Cost) OFFMARC_Cost, Month, Year,EquipmentID from Marc_Cost
Where Year >= 2024 and  Cost_Element <> '0000555090' 
GROUP BY Month, Year,EquipmentID) a

GROUP BY a.Month, a.Year, a.EquipmentID) B

LEFT OUTER JOIN v_marc_equipment EQ
ON RIGHT(B.EquipmentID,9) = EQ.Equipment_Numnber