Joins in conditional statement in SQL Query

Question

I have scenario like, I need to add my join in conditional

e.g. When value is 1 then inner join to table a and if value 2 then inner join to table

below is my sample query and I am getting error Incorrect syntax near the keyword 'inner'

DECLARE @i INT=1
select name from emp
if(@i=1) 
begin
inner join a on a.ID=emp.ID
end
else if(@i=1) 
begin
inner join b on b.ID=emp.ID
end
where emp.ID=2

Thanks

Answer 1

Would this work? Outer join both tables in both conditions and put the conditional in your select clause like so:

select name, case when @i=1 then a.column else b.column end from emp
left outer join a on a.ID=emp.ID
left outer join b on b.ID=emp.ID
where emp.ID=2

Answer 2

As you found out the construct is illegal in SQL. An IF statement cannot be included in a DML query. One can, however, write two separate queries (introducing in the process a bit of duplication) and have one or the other conditionally selected.

DECLARE @i INT
SET @i=1
if(@i=1) 
  begin
      select name from emp
      inner join a on a.ID=emp.ID
      where emp.ID=2
  end
  else if(@i=2) 
  begin
      select name from emp
      inner join b on b.ID=emp.ID
      where emp.ID=2
  end

Answer 3

check if this helps