CODEWITHSUNDEEP
sqlsql-serverms-access
betsvigi9
betsvigi9

Reputation: 31

Left Join not working

I am a SQL Server beginner and so I have been using MS Access to create queries and then have been amending them for SQL Server.

I have created a query which includes a left join, which works perfectly in Access but when I copy the SQL code across to SQL Server it does not show the null values and I cannot work out why.

I want to show all of the attribute values whether they include a value or not. Can anyone help? The code is as follows:

SELECT DISTINCT 
    tbLease.LeaseTitle
    , tbAttributeValue.AttributeTemplateDefinitionLinkID
    , tbAttributeValue.Value
FROM 
   ((((tbBusinessUnit 
LEFT JOIN 
   tbAttributeValue ON tbBusinessUnit.BusinessUnitID = tbAttributeValue.ParentID) 
INNER JOIN 
   tbBuildingLinkBusinessUnit ON tbBusinessUnit.BusinessUnitID  = tbBuildingLinkBusinessUnit.BusinessUnitID) 
INNER JOIN 
   tbBuilding ON tbBuildingLinkBusinessUnit.BuildingID = tbBuilding.BuildingID) 
INNER JOIN 
    (tbUnitLocation 
INNER JOIN 
    tbUnit ON tbUnitLocation.UnitID = tbUnit.UnitID) 
              ON tbBuilding.BuildingID = tbUnitLocation.LocationID) 
INNER JOIN 
    tbLease ON tbUnit.UnitID = tbLease.UnitID
WHERE 
    (((tbAttributeValue.AttributeTemplateDefinitionLinkID) = 30 
      Or (tbAttributeValue.AttributeTemplateDefinitionLinkID) = 31 
      Or (tbAttributeValue.AttributeTemplateDefinitionLinkID) = 32));

Upvotes: 3

Views: 88

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93704

Move the right table filters from where clause to ON condition when you are using Left Outer Join else Left join will be implicitly converted to INNER JOIN. Try this

SELECT DISTINCT tblease.leasetitle, 
                tbattributevalue.attributetemplatedefinitionlinkid, 
                tbattributevalue.value 
FROM   tbbusinessunit 
       LEFT JOIN tbattributevalue 
              ON tbbusinessunit.businessunitid = tbattributevalue.parentid 
                 AND ( tbattributevalue.attributetemplatedefinitionlinkid IN 
                       ( 30, 31, 32 ) 
                        OR tbattributevalue.attributetemplatedefinitionlinkid IS 
                           NULL ) 
       INNER JOIN tbbuildinglinkbusinessunit 
               ON tbbusinessunit.businessunitid = 
                  tbbuildinglinkbusinessunit.businessunitid 
       INNER JOIN tbbuilding 
               ON tbbuildinglinkbusinessunit.buildingid = tbbuilding.buildingid 
       INNER JOIN tbunitlocation 
               ON tbbuilding.buildingid = tbunitlocation.locationid 
       INNER JOIN tbunit 
               ON tbunitlocation.unitid = tbunit.unitid 
       INNER JOIN tblease 
               ON tbunit.unitid = tblease.unitid

Upvotes: 0

CactusCake
CactusCake

Reputation: 990

The NULL values you would normally see with a left join are getting filtered out by your WHERE clause.

Any time you add filters to a WHERE clause that apply to an outer joined table, it will effectively make it the same as an inner join unless you include NULL values as an option in your where clause as well.

SELECT DISTINCT 
tbLease.LeaseTitle,
tbAttributeValue.AttributeTemplateDefinitionLinkID,
tbAttributeValue.Value

FROM 
tbBusinessUnit 
LEFT JOIN tbAttributeValue ON tbBusinessUnit.BusinessUnitID = tbAttributeValue.ParentID 
INNER JOIN tbBuildingLinkBusinessUnit ON tbBusinessUnit.BusinessUnitID  = tbBuildingLinkBusinessUnit.BusinessUnitID 
INNER JOIN tbBuilding ON tbBuildingLinkBusinessUnit.BuildingID = tbBuilding.BuildingID
INNER JOIN tbUnitLocation ON tbBuilding.BuildingID = tbUnitLocation.LocationID
INNER JOIN tbUnit ON tbUnitLocation.UnitID = tbUnit.UnitID        
INNER JOIN tbLease ON tbUnit.UnitID = tbLease.UnitID

WHERE 
tbAttributeValue.AttributeTemplateDefinitionLinkID in (30, 31, 32)
or tbAttributeValue.AttributeTemplateDefinitionLinkID is null

Upvotes: 1

Hogan
Hogan

Reputation: 70523

Hard to say what you want without some examples but you might want this:

WHERE coalesce(tbAttributeValue.AttributeTemplateDefinitionLinkID,30) in (30,31,32);

This will give you items where AttributeTemplateDefinitionLinkID is null (that is it did not join on the left join) OR is one of those 3 values.

Right now if you don't join with the left join it will not display that row because of the where condition, so your left join is the same as an inner join.

Upvotes: 1

Related Questions