Solved

SQL JOIN statement

Posted on 2014-03-19
3
467 Views
Last Modified: 2014-03-19
I have never seen this type of SQL join statement before. It seems very complicated, and I'm very confused by how it works. Can someone explain it?

select   
  CASE WHEN @groupBy = 1 THEN 'Salesperson: '    
   WHEN @groupBy = 2 THEN 'Third Party Salesperson: '    
  END + ISNULL(gb.Name1 + ' (' + gb.Code + ')', '(none)') as GroupByName,  
  SubmittedByName,  CurrentMonth
from @loanInfoTotals  t  
  left join Entity gb on (@groupBy = 0 and 1 = 2)     
  or (@groupBy = 1 and gb.PartyId = t.SalespersonId)     
  or (@groupBy = 2 and gb.PartyId = t.ThirdPartySalespersonId)  

Open in new window

0
Comment
Question by:pzozulka
  • 2
3 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39941015
It appears to be an overly complex attempt at a dynamic query. It's using a variable named @groupby to control which field is being returned, and also how which fields in Entity and @loanInfoTotals (which I assume is a table variable declared elsewhere) are being used for the join.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 39941049
I get all of that except the join logic.

FROM tableA a
left join tableB b on (@groupby = 0 and 1 = 2)
  or (@groupBy = 1 and gb.PartyId = t.SalespersonId)    
  or (@groupBy = 2 and gb.PartyId = t.ThirdPartySalespersonId)  


The stuff in bold is what's really bothering me because normal join statements are joined ON LeftTable.Id = RightTable.Id. In this case, I don't know what @groupby = 0 and 1 = 2 means.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39941074
The @groupby = 0 and 1 = 2 part basically means don't do a join. So, if the variable @groupby is equal to zero, then the 1=2 basically short circuits the join. Whereas if @groupby is either 1 or 2 it joins on either SalespersonId or ThirdPartySalespersonId respectively.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question