Conditional Join in SQL Server stored procedure

Question

I have 3 variables which filter1, filter2, filter3 which are based on user input. Whose value will be either 1 or 0.

And the tables are

• userlist (userid, name, filtercount1, filtercount2, filtercount3)`
• filtertable1 (id, filtercount1)
• filtertable2 (id, filtercount1)
• filtertable3 (id, filtercount1)

Now I want to write a query such that:

• if filter1 = 1 then, inner join user to filtertable1
• if filter2 = 1 then, inner join user to filtertable2
• if filter3 = 1 then, innerjoin user to filtertable3.
  

Now How do I conditionally add innerjoin conjunctively to the above scenario, or is there any other way for the above problem statement.

I've tried the below solution,

Select * 
from 
    users
left join 
    filtertable1 on users.filtercount1 = filtertable1.filtercount1 
                 and filter1 = 1
left join 
    filtertable2 on users.filtercount2 = filtertable3.filtercount2 
                 and filter2 = 1
left join 
    filtertable3 on users.filtercount3 = filtertable3.filtercount3 
                 and filter3 = 1

But I don't want to use left join. And is it such that if data is larger(20000 rows) than left join will take more time than inner join for the above scenario.

Answer 1

The left join is fine. But you can also use exists:

Select u.*
from users u
where (u.filter1 = 1 and
       exists (select 1 from filtertable1 ft where u.filtercount1 = ft.filtercount1)
      ) or
      (u.filter2 = 1 and
       exists (select 1 from filtertable2 ft where u.filtercount2 = ft.filtercount1)
      ) or
      (u.filter3 = 1 and
       exists (select 1 from filtertable3 ft where u.filtercount3 = ft.filtercount1)
      ) ;

Note: I'm not sure if you want and or or between the conditions. With and the logic would be slightly different.