Sql-server – Ignore the joins without any result in SQL Server

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

Best Answer

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
    ;
Related Question