case statement in Join

Question

0.00/5 (No votes)

See more:

SQL

select col1
from table1
case when @col2 is null then left outer join else join end
table2 on (join condition)

Above is my query, i want to choose between left outer or right outer join based on 1 condition.

I want to know a better solution for implementing the above problem

Posted 5-Mar-13 21:40pm

Bharath from India

Updated 5-Mar-13 21:43pm

gvprabu

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-03-05T21:46:00

Solution 1

Hi,

Use Dynamic Query.... Search in Google

how to write a dynamic query in sql[^]

Posted 5-Mar-13 21:46pm

gvprabu

Davidduraisamy · Answer 2 · 2013-03-05T22:02:00

Solution 2

SQL

DECLARE @col2 INT,@join VARCHAR(100),@query NVARCHAR(1000)
SELECT @join=CASE WHEN @col2 IS NULL THEN 'left outer join' ELSE 'join'END
SELECT @query='select *
from [table1] '+@join +
 ' [table2] c on [Join condition]'
 EXEC SP_EXECUTESQL @query

Posted 5-Mar-13 22:02pm

Davidduraisamy

Aarti Meswania · Answer 3 · 2013-03-05T22:14:00

Two ways
1. (example)

SQL

declare @col2 int
set @col2 = NULL ; -- value is null it will give result for LEFT OUTER JOIN only
--change value null to something else it will give result for JOIN only

with a as
(select 1 as a
union all
select 2 as a)


select *
from a
left outer join
(select 1 as a, 'a' as b) as b on a.a=b.a
where
a.a =( case when @col2 is null then a.a else b.a end)

so,as per solution 1 set your query like this

SQL

select col1
from table1
left outer join
table2 on table1.col1 =table2.col1
where table1.col1 = (case when @col2 is null then table1.col1 else table2.col1 end)

2.

SQL

if (@col2 is null)
    begin
        select col1
        from table1
        left outer join 
        table2 on table1.col1=table2.col1
    end
else
    begin
        select col1
        from table1
        join 
        table2 on table1.col1=table2.col1
    End

Happy Coding!
:)