• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

SQL JOIN statement

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?

  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

  • 2
1 Solution
Carl TawnSystems and Integration DeveloperCommented:
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.
pzozulkaAuthor Commented:
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.
Carl TawnSystems and Integration DeveloperCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now