CODEWITHSUNDEEP
sqlsql-server
NoviceToDotNet
NoviceToDotNet

Reputation: 10805

why the left join failing in sql server?

I have following views created

create view vw_AllCatSpInst    
as    
select 
    s1.InstructionID as ItemID, 
    s1.CatID, s1.Description as ItemName, 
    s2.MainPrice, s2.PriceFor     
from SpecialInstruction s1 left join 
     Prices s2 on s1.InstructionID=s2.SourceID 
where s2.PriceFor=4 and s1.IsDeleted=0    

create view vw_SpInstCustomziation   
as    
    select s1.ProductID, 
           s2.SourceID, 
           s1.IsDefaultDisplay, 
           s1.IsDefault, 
           s1.DefaultFreeCount,  
           s1.CustomCategoryID 
    from dbo.ProductCustomizationMaster s1 inner join 
         ProductCustomizationDetail s2    
         on s1.CustomCategoryID=s2.CategoryID     
         and s1.ProductID=s2.ProductID 
         and s1.IsDefaultDisplay=1 and s1.CustomType=3

when i am querying these two views following way it fails the condition s1.CatID=@catID and fetches all left join records.

select s1.ItemID, s1.CatID, s2.ProductID, s1.ItemName, s1.MainPrice, s2.IsDefaultDisplay, s2.IsDefault as IsLimited  
from vw_AllCatSpInst s1 left join 
     vw_SpInstCustomziation  s2   
     on  s1.ItemID=s2.SourceID 
     and s1.CatID=s2.CustomCategoryID  
where s1.CatID=@catID 
and   s2.productid=@prodID 
or    s2.productid is null

Please help me with this.

Upvotes: 0

Views: 253

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your first view has this query:

select s1.InstructionID as ItemID, 
          s1.CatID, s1.Description as ItemName, 
    s2.MainPrice, s2.PriceFor     
from SpecialInstruction s1 left join 
     Prices s2
     on s1.InstructionID=s2.SourceID 
where s2.PriceFor=4 and s1.IsDeleted=0 ;

The whereclause has a condition ons2.PriceFor. This condition does not test ofNULL. So, although theleft joinworks correctly, the non-matching records haveNULLvalues. To fix this, move the condition on the second table into theon` clause:

select s1.InstructionID as ItemID, 
          s1.CatID, s1.Description as ItemName, 
    s2.MainPrice, s2.PriceFor     
from SpecialInstruction s1 left join 
     Prices s2
     on s1.InstructionID=s2.SourceID and s2.PriceFor=4 
where s1.IsDeleted=0 ;

Do not move the condition on s1 into the on clause.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

When you filter on a left joined table in the where clause, your join effectively becomes an inner join. You have to apply that filter as a join condition. So this:

from vw_AllCatSpInst s1 left join vw_SpInstCustomziation  s2   
on s1.ItemID=s2.SourceID and s1.CatID=s2.CustomCategoryID  
where s1.CatID=@catID and s2.productid=@prodID or s2.productid is null

should be: 

from vw_AllCatSpInst s1 left join vw_SpInstCustomziation  s2   
on s1.ItemID=s2.SourceID and s1.CatID=s2.CustomCategoryID  
and s2.productid=@prodID or s2.productid is null 
where s1.CatID=@catID

Actually it should probably be this:

from vw_AllCatSpInst s1 left join vw_SpInstCustomziation  s2   
on s1.ItemID=s2.SourceID and s1.CatID=s2.CustomCategoryID  
and 
(
s2.productid=@prodID or s2.productid is null 
)
where s1.CatID=@catID

Upvotes: 2

Related Questions