Inner or Left Join

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
thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slubekCommented:
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 AnalystAuthor Commented:
I was trying to avoid doing what you suggest. I thought there might be another way.

It is a big query.
ste5anSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeCommented:
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 AnalystAuthor Commented:
Thanks for your help.

Slubek, your way will work to.

Jim Horn, you read my mind on your suggestion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.