Inner or Left Join

thayduck
thayduck used Ask the Experts™
on
I have a parameter called Tab1or3.
 If this parameter = 9, I want the below to be a Left Join, otherwise leave it as a Inner join.

Can that be done some way ?

INNER JOIN #CreditStatus1 c
                     ON Substring(s.custno, 2, 6) = c.custno
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't know how do you want to use that query, but maybe you should try IF..ELSE?

if @tab1or3=9
BEGIN
  SELECT (...) 
    LEFT JOIN #CreditStatus1 c
      ON Substring(s.custno, 2, 6) = c.custno
END
else
BEGIN
  SELECT (...) 
    INNER JOIN #CreditStatus1 c
      ON Substring(s.custno, 2, 6) = c.custno
END

Open in new window

thayduckProgrammer Analyst

Author

Commented:
I was trying to avoid doing what you suggest. I thought there might be another way.

It is a big query.
Senior Developer
Commented:
Using a predicate on the joined table will make it behave like an INNER JOIN:

SELECT  *
FROM    yourTable S
        LEFT JOIN #CreditStatus1 c ON SUBSTRING(s.custno, 2, 6) = c.custno
WHERE   @tab1or3 = 9
        AND C.anyColumn IS NOT NULL;

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Wouldn't there need to be an OR somewhere to handle if @tab1or3 is not 9?
Something like (building on the above)
SELECT  *
FROM yourTable S
    LEFT JOIN #CreditStatus1 c ON SUBSTRING(s.custno, 2, 6) = c.custno
WHERE   
   (@tab1or3 = 9 AND C.JoinColumn IS NOT NULL)  --  simulates INNER JOIN 
   OR @tab1or3 <> 9                             --  keeps it a LEFT JOIN

Open in new window

Nice solution, and the IF block will also work.
thayduckProgrammer Analyst

Author

Commented:
Thanks for your help.

Slubek, your way will work to.

Jim Horn, you read my mind on your suggestion.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial