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


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 ,

more ▼

asked May 19 at 06:55 PM in Default

avatar image

11 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.


more ▼

answered May 21 at 01:52 AM

avatar image

17.5k 3 7 28

(comments are locked)
10|1200 characters needed characters left

Thank you so much Oleg. A great help.

more ▼

answered May 24 at 07:27 PM

avatar image

11 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: 5 days ago

Seen: 16 times

Last Updated: 11 hours ago

Copyright 2016 Redgate Software. Privacy Policy