Ignore as junções sem nenhum resultado no SQL Server

Estive lendo e relendo a pergunta original. O que eu cheguei é isso:

Retorna registros de tblProjecten onde existem correspondências para parâmetros que foram fornecidos (não são nulos). Onde os parâmetros não são fornecidos (ou nulos), assuma todas as correspondências para essa tabela filho

Efetivamente, você está tornando suas associações dinâmicas com base no fato de um parâmetro ter um valor fornecido ou não.

Para isso você pode usar:

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select p.* 
from tblProjecten p 
where p.naam like '%' + @trefwoord + '%'
and ( 
        @themaid is null
    or exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenOpleidingsType pot where p.projectId = pot.projectID and ps.studiegebiedid = @studiegebiedid
)
and (
        @opleidingdtypeid is null
    or exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid
)
and (
        @organisatorid is null
    or exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid
)
and (
        @regioid is null
    or exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid
)

Eu mudei um pouco as coisas. Em vez de fazer joins, estamos fazendo um EXISTScom uma subconsulta correlacionada.

Cada um avalia como:

• Se @parameter for nulo, retorne verdadeiro
• Ou... Se @parameter não for nulo, verifique se existe um registro correspondente na tabela filha para esse @parameter. Se houver, retorne verdadeiro

Ao fazer isso, nossas junções se tornam efetivamente opcionais (dependendo se temos um valor de parâmetro ou não).

Se eu tiver sua intenção original correta, uma suposição fácil é que você pode querer usar junções internas, por exemplo:

select distinct p.* 
from tblProjecten p
inner join tblProjectenThema pt on p.projectId = pt.projectId
...etc...
where p.naam like '%' + @trefwoord + '%'
and (pt.themaId = coalesce(@themaid))

A razão pela qual você não pode fazer isso é que tblProjectenThema pode estar vazio e, portanto, nenhuma linha pode ser correspondida. Você sempre obteria um resultado vazio.

Abordagem alternativa

A consulta acima começará a sofrer com o desempenho, pois mais tabelas precisam ser consideradas. Você poderia usar OPTION(RECOMPILE)contra a consulta. Como você deseja parâmetros opcionais e dinâmicos, isso também pode ser atendido usando SQL dinâmico.

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select @trefwoord = '%' + @trefwoord + '%';
declare @sql nvarchar(max);

select @sql = '
select p.* 
from tblProjecten p 
where p.naam like @trefwoord
'

if @themaid is not null
select @sql += 'and exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)'

if @studiegebiedid is not null 
select @sql += 'and exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid)'

if @opleidingdtypeid is not null
select @sql += 'and exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid)'

if @organisatorid is not null
select @sql += 'and exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid)'

if @regioid is not null
select @sql = +='and exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid)'

exec sp_executesql 
    @stmt = @sql
    ,@params = N'@trefwoord nvarchar(max), @themaid int, @studiegebiedid int, @opleidingdtypeid int, @doelgroepid int, @organisatorid int, @regioid int'
    ,@trefwoord = @trefwoord
    ,@themaid = @themaid
    ,@studiegebiedid = @studiegebiedid
    ,@opleidingdtypeid = @opleidingdtypeid
    ,@doelgroepid = @doelgroepid
    ,@organisatorid = @organisatorid
    ,@regioid = @regioid
    ;

Eu acho que você está usando LEFTjoins incorretamente. Do jeito que a consulta está agora, com uma coluna de cada tabela envolvida na WHEREcláusula, as junções atuam efetivamente como INNERjunções, não LEFTjunções. Como @Kent explicou no comentário:

Quando a condição está na instrução de junção (a ONparte), a restrição é aplicada antes da junção. Quando está no WHERE, é aplicado após a ocorrência da junção (portanto, se você colocar uma condição sobre a tabela correta, a junção efetivamente se tornará uma junção interna).

Agora, eu não tenho certeza sobre o que exatamente é desejado.

Caso A (provavelmente não é o desejado). Se você também deseja informações das outras tabelas, deve:

• mover as condições de WHEREpara as respectivas ONcondições
• livrar-se das COALESCE()funções
• (bônus) livrar-se doDISTINCT

A consulta seria algo como:

select p.*
    -- pt.*, ps.*, ... 
from tblProjecten p 
    left join tblProjectenThema pt
        on  p.projectId = pt.projectId
        and pt.themaId = @themaid 
    left join tblProjectenStudiegebieden ps
        on  p.projectId = ps.projectid
        and ps.studiegebiedid = @studiegebiedid
    left join tblProjectenOpleidingsType pot
        on  p.projectId = pot.projectID
        and pot.opleidingsID = @opleidingdtypeid
    left join tblProjectendoelgroep pd
        on  p.projectId = pd.projectId
        and pd.doelgroepId = @doelgroepid
    left join tblProjectenOrganisator po
        on  p.projectId = po.projectId
        and po.organisatorId = @organisatorid
    left join tblProjectenRegio pr
        on  p.projectId = pr.projectId
        and pr.regioId = @regioid
where  
      p.naam like '%' + @trefwoord + '%'
 ;

Caso B (acho que é isso). Se você deseja "parâmetros opcionais", ou seja, quando os parâmetros são nulos para não restringir os resultados, você deve:

• converter as junções e WHEREcondições em EXISTSsubconsultas
• livrar-se das COALESCE()funções e usar OR @parameter IS NULL)
• (bônus) livrar-se doDISTINCT

A consulta seria algo como:

select p.*
from tblProjecten p 
where 
      p.naam like '%' + @trefwoord + '%'
  and ( @themaid is null
      or exists
          ( select * from tblProjectenThema pt
            where p.projectId = pt.projectId
              and pt.themaId = @themaid
          )
      ) 
  and ( @studiegebiedid is null
      or exists
          ( select * from tblProjectenStudiegebieden ps
            where p.projectId = ps.projectId
              and ps.studiegebiedid = @studiegebiedid
          )
      ) 
  -- and ...
 ;