0

I have the following query In which I have all the selected columns in mResourceAllocation and mResource table with foreign keys. In mResourceAllocation foreign key columns CoreFunction_ID (Function) and Product_ID (Product) both are nullable. I used INNER JOINS to get that values from other tables. What I want is to get all the records with entered @businessUnitId and @reportDate including the null entries in CoreFunction_ID column and Product_ID column. Please Help. Thanks in advance.

SELECT Distinct
[Employee ID] = r.Resource_Employee_ID,
[Resource ID] = r.Resource_ID,
[Resource Name] = r.Resource_First_Name+' '+r.Resource_Last_Name,
[Country] = c.Country_Name,
[Location] = l.Location_Short_Name,
[BU] = bu.BusinessUnit_Name,
[Function] = cf.Core_Function_Name,
[Product] = ISNULL (p.Product_Name,'Unknown'),
[Assignment Start Date] = ra.Resource_Allocation_From_Date,
[Assignment End Date] = ra.Resource_Until_Date,
[Time Allocated] = ra.Resource_Allocation_Percentage,
[Training] = ra.Resource_Training,
[Admin] = ra.Resource_Admin,
[Roadmap] = ra.Resource_Roadmap,
[Defect] = ra.Resource_Defect,
[Commission] = ra.Resource_Commission,
[Effectiveness] = ra.Resource_Efficiency_Percentage

INTO #ResourceAllocated 

FROM mResource r
INNER JOIN mResource ON r.IsDeleted = 0
INNER JOIN mResourceAllocation ra ON ra.IsDeleted = 0 AND ra.BusinessUnit_ID = @businessUnitId AND r.Resource_ID = ra.Resource_ID AND @reportDate >= ra.Resource_Allocation_From_Date AND (@reportDate <= ra.Resource_Until_Date  OR ra.Resource_Until_Date IS NULL)
INNER JOIN mBusinessUnit bu ON bu.IsDeleted = 0 AND bu.BusinessUnit_ID = @businessUnitId
INNER JOIN mCountry c ON r.Resource_Country_ID = c.Country_ID
INNER JOIN mLocation l ON r.Resource_Location_ID = l.Location_ID
INNER JOIN mCoreFunction cf ON  ra.CoreFunction_ID = cf.Core_Function_ID
INNER JOIN mProduct p ON p.IsDeleted = 0 AND ra.Product_ID = p.Product_ID
Renascent
  • 184
  • 1
  • 3
  • 17

1 Answers1

0

instead of

INNER JOIN mCoreFunction cf ON  ra.CoreFunction_ID = cf.Core_Function_ID 
INNER JOIN mProduct p ON p.IsDeleted = 0 AND ra.Product_ID = p.Product_ID

use

LEFT JOIN mCoreFunction cf ON  ra.CoreFunction_ID = cf.Core_Function_ID
LEFT JOIN mProduct p ON p.IsDeleted = 0 AND ra.Product_ID = p.Product_ID
Stephan Lechner
  • 34,891
  • 4
  • 35
  • 58
  • Thanks a lot. That worked well. but it takes much more time to execute my stored procedure but its fine as I get my desired result. I guess Left joins takes more time to execute. – Renascent Dec 22 '16 at 09:10
  • Left joins are usually less performant than inner joins. Please cf. http://stackoverflow.com/a/2726683/2630032, which explains the different execution plans for inner join and outer join; also it gives some hints like, for example, checking indexes. Enjoy :-) – Stephan Lechner Dec 22 '16 at 09:21