Ignore the joins without any result in SQL Server Situation
I'm making a SQL server query to filter data from the projects table (tblProjecten). This table got a lot of many to many relationships to other tables like region (tblProjectenRegio), organisation (tblProjectenOrganisatie), etc.
All the parameters I declare are optional. I've found to use coalesce to do this. It works fine if I don't use the joins inside my query here you got my query I've use first:
declare @themaid int             = 1        ; -- themeID
declare @trefwoord nvarchar(max) = ''       ; -- search query

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

Below my results for the different declarations (see also data below):




@themaid
@trefwoord
results
correct




1

166
OK


1
airco
166
OK


null
airco
166
OK


null

166, 185 , 415
OK




This works fine but If I add other conditional parameters like query below. I got totally other results.
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 distinct p.* 
from tblProjecten p left join tblProjectenThema pt                 on p.projectId = pt.projectId
                    left join tblProjectenStudiegebieden ps        on p.projectId = ps.projectid
                    left join tblProjectenOpleidingsType pot       on p.projectId = pot.projectID
                    left join tblProjectendoelgroep pd             on p.projectId = pd.projectId
                    left join tblProjectenOrganisator po           on p.projectId = po.projectId
                    left join tblProjectenRegio pr                 on p.projectId = pr.projectId
where pt.themaId        = coalesce(@themaid, pt.themaId)                 and
      ps.studiegebiedid = coalesce(@studiegebiedid, ps.studiegebiedid)   and
      pot.opleidingsID  = coalesce(@opleidingdtypeid, pot.opleidingsID)  and
      pd.doelgroepId    = coalesce(@doelgroepid, pd.doelgroepid)         and
      po.organisatorId  = coalesce(@organisatorid, po.organisatorId)     and
      pr.regioId        = coalesce(@regioid, pr.regioId)                 and
      p.naam like '%' + @trefwoord + '%'                                    ;

Here are the results (the other declarations are null):




@themaid
@trefwoord
results
correct
must be




1


NOT OK
166


1
airco

NOT OK
166


null
airco

NOT OK
166


null


NOT OK
166, 185, 415




This comes because the other tables haven't any data inside it. (see data below)

Question
My question is now can I ignore the joins without any result to make the last query working?
I've also tries to use inner and right joins but give the same results.

Data
Here you got some data:
tblProjecten:




projectId
naam




166
Attestering AIRCO PROJECT


185
Autoweb E-LEARNING


415
Bouw en Hout




tblProjectenThema:




themaId
projectId




1
166


2
166


2
415


3
415


6
185




tblProjectendoelgroep:




doelgroepId
projectId
I've been reading and re-reading the original question.  What I have come to is this:

Return records from tblProjecten where matches exist for parameters that have been supplied (are not null).  Where the parameters are not supplied (or null) then assume all matches for that child table

Effectively you're making your joins dynamic based on whether a parameter has a supplied value or not.

For that you can use:

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
)


I have changed things around a little.  Instead of doing joins we're doing an EXISTS with a correlated subquery.  

Each one evaluates as:


If @parameter is null return true
Or... If @parameter is not null then check if there exists a matching record on the child table for that @parameter. If there is, return true


By doing this our joins effectively become optional (depending on whether we have a parameter value or not).

If I have your original intent correct, then an easy assumption is that you might want to use inner joins e.g.:

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))


The reason you cannot do this is that tblProjectenThema could be empty and therefore no rows can ever be matched.  You would always get an empty result.

Alternative approach

The query above will start to suffer from performance as more tables need to be considered.  You could use OPTION(RECOMPILE) against the query.  Because you want optional, dynamic, parameters this can also be served by using dynamic SQL.

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
    ;