SQL Server left join if condition met

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

You can try below query.

First method:

select
    works.*, isnull(users.id, groups.id)
from 
    works with(nolock)
left outer join 
    users with(nolock) on users.id = works.owner and work.type = 1
left outer join 
    groups with(nolock) on groups.id = works.owner

Second method:

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

third method: use dynamic SQL to build query at runtime.

Considering that you want to display all columns (*), then you can conditionally join against both tables by checking the work.type as a join condition:

select 
    * 
from 
    works 
    left join users on 
        users.id = work.owner and 
        work.type = 1
    left join groups on 
        groups.id = work.owner and 
        (work.type <> 1 OR work.type IS NULL)

A particular row from the works table can only have a particular value for type, so it will join against users or against groups, but never both. The problem with this solution becomes the displayed columns, since we are joining against both tables, now you have to unify groups and users columns.

You can do this with a bunch of ISNULLs:

select 
    works.*,
    Column1 = ISNULL(users.Column1, groups.Column1), 
    Column2 = ISNULL(users.Column2, groups.Column2)
from 
    works 
    left join users on 
        users.id = work.owner and 
        work.type = 1
    left join groups on 
        groups.id = work.owner and 
        (work.type <> 1 OR work.type IS NULL)

If you need to use the same select repeatedly, you can create a table-valued function to wrap this up so you don't have to code it every time. I'll use the table example to show another alternative, using UNION ALL.

CREATE FUNCTION dbo.GetWorkData (@owner INT) -- assuming its a INT
RETURNS TABLE
AS
RETURN

    SELECT
        -- Your wanted columns here
    FROM
        works AS W
        INNER JOIN users AS U ON W.owner = U.owner
    WHERE
        W.owner = @owner AND
        W.type = 1

    UNION ALL

    SELECT
        -- Your wanted columns here (must be same data type and order of previous SELECT)
    FROM
        works AS W
        INNER JOIN groups AS U ON W.owner = U.owner
    WHERE
        W.owner = @owner AND
        (W.type <> 1 OR W.type IS NULL)

You can use the function with APPLY:

SELECT
    D.*
FROM
    works AS W
    CROSS APPLY dbo.GetWorkData(W.owner) AS D -- User "OUTER APPLY" if you want works that have no users or groups

You can look into Dynamic SQL. The main idea is that the SQL statement is constructed and compiled at runtime.

You can start here: Link 1 - MSSQL tips.

or here: Link 2 - official microsoft documentation.