May 19th, 2017, 12:06 PM
Join Date: May 2017
Time spent in forums: 17 m 22 sec
Reputation Power: 0
Joined tables return all instances in table 1
A complete newbie and have to extract some data for a migration project. Looking for some help as my head is fried.
I have two tables :
Unit_0 which is the main data.
UnitFin_0 which is costs associated to the units.
There are numerous costs in the UnitFin table from various orders, AND the key here is that some cost lines have been marked as deleted. I do not want to return the deleted items.
My goal is to return every assetkey from the unit_0 table and also the sum the cost lines from the unitFin_0 table, BUT not the deleted costs.
*I know that some assets do not have any costs associated to them in the unitFin_0 table*.
The issue is that the query will not retrieve units that have no costs associated on the unitFin_0 table. I want the unit to be returned but give me a blank or a zero in the sum amount column.
I choose '405x2000' as an example unit because I know it has no costs. If I just use the:
WHERE unit_0.unitkey IN ( '405X2000' ) it seems to retrieve the asset (however this would also include deleted cost lines in the bigger query). If I add in the additional "only non deleted costs" i.e. AND unitFin_0.Deleted=0 the units disappear from the results.
Please can someone help this tired old fool.
SELECT unit_0.unitkey AssetKey,
FROM MISMON.PUB.unit unit_0
LEFT JOIN MISMON.PUB.unitFin unitFin_0 ON unit_0.unitkey = unitFin_0.unitkey
WHERE unit_0.unitkey IN ( '405X2000' ) AND unitFin_0.Deleted=0
GROUP BY unit_0.unitkey ,