SQL Server left join if condition met

Question

I have a problem with my query. I have to do left outer join only if condition is true. If condition is false do another left outer join.

I tried with this but not successfully:

select 
    * 
from 
    works with(nolock) 
if work.type = 1 
begin
    left outer join 
        users with(nolock) on users.id = work.owner
else
    left outer join 
        groups with(nolock) on groups.id = work.owner
end

How can I solve this problem?

Answer 1

you should try left join both of them, but inside of the select, take what you want depending on the use case.

SELECT
  *, 
  CASE work.type WHEN '1' THEN 'a.owner' ELSE 'b.owner' END AS owner
FROM
  blahblah
  left join users on blahblah.user_id = users.id as a,
  left join groups as blahblah.groups_id = groups.id as b