Using Join based on condition

Question

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.

Do you mean *else if* instead of *and if* ? – StuartLC Aug 24 '12 at 10:27 — StuartLC, Aug 24 '12 at 10:27

score 1 · Accepted Answer · edited May 23 '17 at 12:20

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

edited May 23 '17 at 12:20

Community

1
1

answered Aug 24 '12 at 10:31

StuartLC

104,537
17
209
285

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 – Chaman Shukla Aug 24 '12 at 10:34
@champ - updated with dynamic sql option. I've assumed sql server from your earlier post. – StuartLC Aug 24 '12 at 10:40
Hey nonnb Thank you so much i think dynamic sql is better option. Thnx again – Chaman Shukla Aug 24 '12 at 10:41
1

@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. – StuartLC Aug 24 '12 at 10:47

score 0 · Answer 2 · edited Aug 24 '12 at 18:39

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.

edited Aug 24 '12 at 18:39

Jake1164

12,291
6
47
64

answered Aug 24 '12 at 10:25

Ariel

25,995
5
59
69

davek · Answer 3 · 2012-08-24T10:55:43.077

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.

edited Aug 24 '12 at 10:55

answered Aug 24 '12 at 10:30

davek

22,499
9
75
95

score 0 · Answer 4 · edited Aug 24 '12 at 18:36

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

edited Aug 24 '12 at 18:36

Jake1164

12,291
6
47
64

answered Aug 24 '12 at 11:07

Arun Antony

36
1

Using Join based on condition

4 Answers4