1

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
5
  • 1
    Do you select data from the first table only? I see SELECT p.* and no more additional columns. Then, why do you use left joins if no columns are selected from them, just tu multiply the rows of the final resultset?
    – sepupic
    Nov 23, 2017 at 9:39
  • @sepupic: Yes I'll have the results from my first table. What you mean with multiply the rows of the final resultset? (I'm a junior.) Nov 23, 2017 at 9:48
  • 3
    I think you want to use LEFT joins correctly, i.e. move the conditions from WHERE to the respective ON conditions. Nov 23, 2017 at 9:48
  • 2
    Can you provide some examples of values and the expected results you want to see. I'm unclear what you are trying to achieve. I can tell its something like: "Show me projects that have...." With LEFT joins you always get all rows from the LEFT side after the join. A WHERE clause may then filter out some of those rows. Nov 23, 2017 at 9:56
  • @KentChenery I think you are right. I didn't read the question properly. I think they want optional parameters Nov 23, 2017 at 10:11

2 Answers 2

4

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
    ;
1
  • 2
    Nice. I was editing (with an almost identical query) while you answered. Nov 23, 2017 at 10:23
1

I think you are using LEFT joins incorrectly. The way the query is now, with a column from each table involved in the WHERE clause, the joins effectively act as INNER joins, not LEFT joins. As @Kent explained in comment:

When the condition is in the join statement (the ON part) the restriction is applied before the join. When it's in the WHERE, it's applied after the join occurs (so if you put a condition about the right table, the join effectively becomes an inner join).

Now, I'm not sure about what exactly is wanted.


Case A (probably not what is wanted). If you want info from the other tables, too, then you should:

  • move the conditions from WHERE to the respective ON conditions
  • get rid of the COALESCE() functions
  • (bonus) get rid of the DISTINCT

The query would be something like:

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 + '%'
 ;

Case B (I think this is it). If you want "optional parameters", i.e. when the parameters is null to not restrict the results, then you should:

  • convert the joins and WHERE conditions to EXISTS subqueries
  • get rid of the COALESCE() functions and use OR @parameter IS NULL)
  • (bonus) get rid of the DISTINCT

The query would be something like:

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 ...
 ;
3
  • Since the joins are all LEFT OUTER joins they become unnecessary as columns are only returned from tblProjecten. The parameters have no effect. I think the OP wants optional parameters, but if provided to then restrict on them. This changes things vastly. Nov 23, 2017 at 10:02
  • @KentChenery thnx. I've added some explanation. Feel free to edit by the way. Nov 23, 2017 at 10:03
  • Sorry...I've changed my original comment - although the bits about when restrictions occurs is valid (and helpful). Nov 23, 2017 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.