asked
Joined tables return all instances in unit_0
Hi all, A complete newbie here so go easy :-) I 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 unit '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. Many Thanks Darren QUERY BELOW:_____________________ SELECT unit_0.unitkey AssetKey, unit_0.UnitUID AssetID, SUM(unitFin_0.BaseAmount) AssetValue 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 , unit_0.UnitUID
The problem with your query is very easy to fix. The LEFT join is the correct one to use, but you cannot use it with the combination of any predicates which involve the columns from the right hand side table. For example, the record in the main table with ID = 405X2000 does not have any matching rows in the child table, so the row for this record (from the left table) is going to have NULL for any columns in the right table, but you do require it to be with value of 0 in the Deleted column. This part of the where clause causes the record from the left table to be excluded. It kinda makes the LEFT join to become INNER. In order to get the results you need, just move the part of the where clause reading AND unitFin_0.Deleted=0 to the join condition. This will fix the problem with the query. The join condition should become ON unit_0.unitkey = unitFin_0.unitkey AND unitFin_0.Deleted=0 Hope this helps. Oleg
19 People are following this question.
