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