SQL Join not bringing in exclusive rows based on a condition

Question

I am trying to apply a Left Join between two tables.

This is the First Table:

This is the Second Table:

Both are joined by WorkTypeId column.

Now the requirement is as such that if there is an entry in Table2 based on some userid then I should get all the worktypeid from Table 2 for that userid and the remaining worktypeid should come from Table1 with rest of the columns as 0 or 1.

Below is the query which i am trying to apply:

IF EXISTS (SELECT TOP 1 userid FROM Table2 WHERE UserId = @UserId)  
BEGIN     
Print 'inside If'          
            Select L.WorkType as WorkTypeName, UW.WorkTypeId,UW.IsChecked,UW.Active,UW.Priority                   
             From Table1 L         
             Inner Join Table2 UW            
             On L.WorkTypeId = 1 --UW.WorkTypeId                
             Where UserId =99 ---@userId                  
             And L.Active = 1                    
             --Order By WorkTypeName ASC   
        UNION     
            SELECT L.WorkType as WorkTypeName,UW.WorkTypeId,0 as IsChecked,1 as Active,0 as Priority     
             From Table1 L          
             Left Join Table2 UW          
             On L.WorkTypeId =1 --UW.WorkTypeId                
             Where UserId =99  -- @userId                  
             And L.Active = 1                
            AND UW.Priority IS NULL    
END

But still I am seeing only one row which is the matching row and not other rows from left table along with hardcoded values.

EDIT 1 :

I have changed the query to below

Select L.WorkType as WorkTypeName, L.WorkTypeId,UW.IsChecked,UW.Active,UW.Priority                   
             From Table1 L         
             Inner Join Table2 UW            
             On L.WorkTypeId = UW.WorkTypeId              
             Where UserId =99 ---@userId                  
             And L.Active = 1 
             --Order By WorkTypeName ASC   
        UNION     
            SELECT L.WorkType as WorkTypeName,L.WorkTypeId,0 as IsChecked,1 as Active,0 as Priority     
             From Table1 L
             Left Join Table2 UW                     
             On UW.WorkTypeId = L.WorkTypeId
             AND L.WorkTypeId Not In (Select WorkTypeId From UserWorkType Where UserId =99) --@UserId)
             AND L.Active = 1

But still getting rows from both tables

I dont need the one marked with RED as this is already present in Table2.

When you put a filter on the inner table you effectively make it an inner join. So either use some kind of derived table or cte to filter the results to the specific userid prior to the join or move that condition into the join itself. — shawnt00, Aug 17 '20 at 20:39
`WHERE UserId = 99`. You pretty much filter out records which have UserId `NULL`. — Eric, Aug 17 '20 at 21:16
thanks a lot. Though not directly but this is a good link btw i have found the answer, please read my own answer here. I don't know who has downvoted my question as i have been stating each and every step along with my efforts. — rp4361, Aug 17 '20 at 22:32

Gordon Linoff · Answer 1 · 2020-08-18T01:13:57.740

1

This should work by moving the filtering condition to the on clause:

select L.WorkType as WorkTypeName, UW.WorkTypeId, UW.IsChecked, UW.Active, UW.Priority                   
from Table1 L left join           
     Table2 UW
     on L.WorkTypeId = UW.WorkTypeId and               
        UW.UserId = 99 ---@userId                  
where L.Active = 1

edited Aug 18 '20 at 01:13

answered Aug 17 '20 at 20:46

Gordon Linoff

1,242,037
58
646
786

Did you miss 'Where' before UW.UserId ? Coz the query is not complete. – rp4361 Aug 17 '20 at 21:13
@Rishabh . . . No. It was missing `and`. – Gordon Linoff Aug 18 '20 at 01:14

score 0 · Answer 2 · edited Aug 17 '20 at 20:25

0

I think using left outer join you should able to get result. Please try below query:

 Select tab2.WorkType as WorkTypeName, tab2.WorkTypeId,tab2.IsChecked,tab2.Active                 
 From Table2 tab2         
 left Join Table1 tab1 On tab2.WorkTypeId = tab1.WorkTypeId                
 Where UserId = @userId                  
 And tab2.Active = 1

edited Aug 17 '20 at 20:25

Dale K

25,246
15
42
71

answered Aug 17 '20 at 20:24

Naresh Chandira

1
1

Thanks. But Tab2 doesn't have WorkType as a column and even if i replace it with tab1 i still don't get the desired result. – rp4361 Aug 17 '20 at 20:35

Olga Romantsova · Answer 3 · 2020-08-17T21:22:25.303

When an inner join is used there is no difference between On and Where clauses. You get the same result from both. But with left joins you do get the difference between On and Where in SQL Server. Try to change where To On:

Select L.WorkType as WorkTypeName, L.WorkTypeId,UW.IsChecked,UW.Active,UW.Priority                   
         From Table1 L         
         Inner Join Table2 UW            
         On L.WorkTypeId = UW.WorkTypeId              
         Where UserId =99 ---@userId                  
         And L.Active = 1 
         --Order By WorkTypeName ASC   
    UNION     
        SELECT L.WorkType as WorkTypeName,L.WorkTypeId,0 as IsChecked,1 as Active,0 as Priority     
         From Table1 L
         Left Join Table2 UW                     
         On UW.WorkTypeId = L.WorkTypeId
         AND L.WorkTypeId Not In (Select WorkTypeId From UserWorkType Where UserId =99) --@UserId)
         AND L.Active = 1
         AND  UW.WorkTypeId is null

Then in your second query you can add this, for example: and UW.WorkTypeId is NULL — Olga Romantsova, Aug 17 '20 at 21:18

score 0 · Answer 4 · answered Aug 17 '20 at 22:26

Please refer to my question for detailed requirements. Thanks a lot for all the answers. As per the requirement below is the query which i have finally created.

The silly thing is I need not to use the Other Table (Table2) instead since I need only EXCLUSIVE records hence I am using a NOT IN. I have kept the commented code so as to keep the understandability.

Select L.WorkType as WorkTypeName, L.WorkTypeId,UW.IsChecked,UW.Active,UW.Priority                   
                     From LuWorktypes L         
                     Inner Join UserWorkType UW            
                     On L.WorkTypeId = UW.WorkTypeId              
                     AND UserId =@userId ---@userId                  
                     And L.Active = 1 
                     --Order By WorkTypeName ASC   
                UNION     
                    SELECT L.WorkType as WorkTypeName,L.WorkTypeId,0 as IsChecked,1 as Active,0 as Priority     
                     From LuWorktypes L
                    -- Left Join UserWorkType UW                     
                    -- On UW.WorkTypeId = L.WorkTypeId
                     Where L.WorkTypeId Not In (Select WorkTypeId From UserWorkType Where UserId = @UserId)
                     AND L.Active = 1

SQL Join not bringing in exclusive rows based on a condition

4 Answers4