Join two tables in SQL Report Builder with mutiple conditions

Question

I am using SQL Report Builder 2016. I have 2 tables, named assets and DepreciationInfo, following is the structure of these tables. Table Assets:

values would be like

123|Name|10000|4000|06/03/2014|True|

Table DepreciationInfo:

values would be like

123|2020-04-30 00:00:00.000|2000|5000|5000|0/1|

I want to achieve following;

I want to select id from table assets, and will show all fields mentioned above from table assets along with fileds from table dep info based on "ID" , Column "ID" is same in both tables.

I am successful in getting all values when Id is common in both table using below mentioned query.

 SELECT
  Assets.ID
  ,Assets.Name
  ,Assets.Cost
  ,Assets.Prior Dep
  ,Assets.Prior Dep Period
  ,Assets.Use Prior
  ,DepreciationInfo.EndDate
  ,DepreciationInfo.CurrentDepreciation
  ,DepreciationInfo.AccumulatedDepreciation
  ,DepreciationInfo.CarryingValue
  ,DepreciationInfo.DepID
  ,DepreciationInfo.Monthly
FROM
  Assets
  INNER JOIN DepreciationInfo
    ON Assets.AssetID = DepreciationInfo.AssetID

where DepreciationInfo.EndDate=@EndDate and DepreciationInfo.Monthly=0

What i want is that i want to show all results from table asset whether or not such id existed in table DepreciationInfo.

I tried all outer joins and result is same, it is showing number of records with Inner and Outer join.

Any help would be appreciated.

Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Mar 19 '18 at 17:29
i will take a look into that, i am not a programmer , i am an auditor trying to learn Report builder as i want to use customized accounting reports. Thank you for quick reply. — Waseem, Mar 19 '18 at 17:32
query is generating same report with LEFT OUTER JOIN, Full Outer join and right Outer join, it is showing 195 values from total of 234 values. — Waseem, Mar 19 '18 at 17:56
your where clause is causing the problems as you are making it an inner join with those consitions — KeithL, Mar 19 '18 at 17:57
i want to use end date as a parameter, as by default dep info table has more than 40 values against same id on different dates ( each month end) so i want to show Current Depreciation on date specified on report page. — Waseem, Mar 19 '18 at 18:01

score 0 · Answer 1 · answered Mar 19 '18 at 17:59

Change your inner join to a left join and you will see all results from table asset whether or not a corresponding assetid value exists in table DepreciationInfo. The only issue here is that the query will still be limited by your two filters in the where clause. I suggest you change them to filter off of fields in the asset table if possible:

SELECT
  Assets.ID
  ,Assets.Name
  ,Assets.Cost
  ,Assets.Prior Dep
  ,Assets.Prior Dep Period
  ,Assets.Use Prior
  ,DepreciationInfo.EndDate
  ,DepreciationInfo.CurrentDepreciation
  ,DepreciationInfo.AccumulatedDepreciation
  ,DepreciationInfo.CarryingValue
  ,DepreciationInfo.DepID
  ,DepreciationInfo.Monthly
FROM
  Assets
  Left JOIN DepreciationInfo
    ON Assets.AssetID = DepreciationInfo.AssetID

where Asset.EndDateorOtherCorrespondingDateValue=@EndDate and Asset.MonthlyorOtherCorrespondingValue=0

thank you, it adds one more page with these changing, i am now checking how to achieve same result with end date parameter. — Waseem, Mar 19 '18 at 18:07

score 0 · Answer 2 · answered Mar 19 '18 at 18:00

0

Move your where into the join and change to left join

 SELECT
  Assets.ID
  ,Assets.Name
  ,Assets.Cost
  ,Assets.Prior Dep
  ,Assets.Prior Dep Period
  ,Assets.Use Prior
  ,DepreciationInfo.EndDate
  ,DepreciationInfo.CurrentDepreciation
  ,DepreciationInfo.AccumulatedDepreciation
  ,DepreciationInfo.CarryingValue
  ,DepreciationInfo.DepID
  ,DepreciationInfo.Monthly
FROM
  Assets
  left JOIN DepreciationInfo
    ON Assets.AssetID = DepreciationInfo.AssetID
       and DepreciationInfo.EndDate=@EndDate 
       and DepreciationInfo.Monthly=0

Returns all assets and the matches to depreciation based on you ON conditions.

You cannot have left joined objects in your where clause or you convert that left join into an inner join (because the nulls are eliminated).

answered Mar 19 '18 at 18:00

KeithL

5,348
3
19
25

thank you, it adds one more page with these changing, i am now checking how to achieve same result with end date parameter. both yours and Daniel answer are same, based on all the info is there any other approach i can use to get data in table form? – Waseem Mar 19 '18 at 18:07
THe @enddate is being used in my query. His query does not have your parameters in it at all. He is suggesting to find a value in your asset table to use as filter in the where clause (which is wrong in your case) – KeithL Mar 19 '18 at 18:09
than i might confuse it with this "where Asset.EndDateorOtherCorrespondingDateValue=@EndDate and Asset.MonthlyorOtherCorrespondingValue=0" – Waseem Mar 19 '18 at 18:10
He's basically saying to find something the asset table that doesn't exist. – KeithL Mar 19 '18 at 18:13
ON Assets.AssetID = DepreciationInfo.AssetID and DepreciationInfo.EndDate=@EndDate and DepreciationInfo.Monthly=0 -- This includes all your parameters – KeithL Mar 19 '18 at 18:13
yes i know, and as he thinks that i probably change the name of fileds so he was trying me to tell that write the actual field name from database, i am working on my parameter and i am looking it closely with database structure that why outer join is also not showing all ids, will get back to you with more detailed reasons, Thank you for your kind response. – Waseem Mar 20 '18 at 06:01

Join two tables in SQL Report Builder with mutiple conditions

2 Answers2