2

Can anyone please explain me how can we use join on the basis of condition. Lets say i am filtering data on the basis of a condition now my concern is if a particular BIT type parameters value is 1 then the data set include one more join else return same as earlier. Here is three tables A,B,C now i want to make a proc which has the @bool bit parameter 

if @bool=0
then  
select A.* from A 
inner join B on B.id=A.id
and if @bool=1 
then 
select A.* from A
INNER JOIN B on B.id=A.id
inner join C on C.id=A.id

Thanks In Advance.

Improve this question
1
  • Do you mean else if instead of and if ? Commented Aug 24, 2012 at 10:27

4 Answers 4

Reset to default
1

What you have will work (certainly in a SPROC in MS SQL Server anyway) with minor mods. 

if @bool=0 then  
   select A.* from A 
   inner join B on B.id=A.id
else if @bool=1 then -- Or just else if @boll is limited to [0,1]
   select A.* from A
   INNER JOIN B on B.id=A.id
   inner join C on C.id=A.id

However, the caveat is that SQL parameter sniffing will cache a plan for the first path it goes down, which won't necessarily be optimal for other paths through your code.

Also, if you do take this 'multiple alternative query' approach to your procs, it is generally a good idea to ensure that the column names and types returned are identitical in all cases (Your query is fine because it is A.*).

Edit Assuming that you are using SQL Server, an alternative is to use dynamic sql:

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'select A.* from A 
             inner join B on B.id=A.id'

IF @bool = 1
    SET @sql = @sql + N' inner join C on C.id=A.id'

sp_executesql @sql

If you need to add filters etc, have a look at this post: Add WHERE clauses to SQL dynamically / programmatically

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I don't want to use these two conditions. Instead of two diffrent conditon cann't we do this in single condition like select A.* from A inner join B on B.id=A.id case when @bool=1 then inner join C on c.id like this i know its wrong way but want similar logic. ThankYou
@champ - updated with dynamic sql option. I've assumed sql server from your earlier post.
Hey nonnb Thank you so much i think dynamic sql is better option. Thnx again
@champ - your welcome! One thing to remember when using dynamic sql is to ensure any variables etc are parameterized (where myfield=@value1 etc). Else you will be vulnerable to SQL injection.
0 
select A.* from A
inner join B on B.id = A.id
left outer join C on C.id = A.id and @bool = 1
where (@bool = 1 and C.id is not null) or @bool = 0

The @bool = 1 "activates" the left outer join, so to speak, and turns it, in effect, into an inner join by applying it in the WHERE clause, too. If @bool = 0 then the left outer join returns nothing from C and removes the WHERE restriction.

Improve this answer

Comments

0

Try the following query

SELECT A.*
FROM A
INNER JOIN B on B.id=A.id
INNER JOIN C on C.id=A.id and @bool=1
Improve this answer

Comments

0

You do it using a union:

SELECT A.* 
FROM A 
    INNER JOIN B on B.id=A.id 
WHERE bool = 0

UNION ALL

SELECT A.* 
FROM A 
    INNER JOIN B on B.id=A.id 
    INNER JOIN C on C.id=A.id 
WHERE bool = 1

I'm assuming that bool is stored in table A or B.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.