Tuesday, October 16, 2012

Allow Null values In Inner join

Recently i got stuck in a scenario where i had to allow nulls in inner join and also sequece needed to be the same as inner join.

For eg:
We have a table with following schema

       Id         Name            ProjectId           TechGroupId
       1           m1                  null                   30
       2            m2                  p1                     null 

Projects :
       Id                            Name
       p1                           Project1
       p2                           Project2   

TechGroups :
       Id                            Name
       1                           dotnet
       2                           java   
       30                         Salesforce 

And user expecting following result:

     Id         Name            Project                  TechGroup
       1           m1                  null                    SalesForce
       2            m2                  project1                     null 


we can create to temptables as following
Select * into #Projects from Projects
select * into #TechGroups from TechGroups

Now add nulls in temp tables.

Insert #Projects
values (null , null)

Insert #TechGroups

and To get expected result query should be:

Select * from PersonalDetails pd
inner join #Projects p on  (pd.Projectid is null and p.Id is null ) or pd.Projectid=p.Id
inner join #TechGroups tg on  (pd.TechGroupid is null and tg.Id is null ) or pd.TechGroupid=tg.Id

No comments:

Post a Comment