Solved

SQL JOIN statement

Posted on 2014-03-19
3
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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