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

P.S.

  1. Some data below this question.
  2. Sorry for the Dutch names but I'm working on an existing database. For translations or if this question is unclear please comment below.
  3. I don't like to write complex, unreadable and long queryies
  • 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 '17 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.) – H. Pauwelyn Nov 23 '17 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. – ypercubeᵀᴹ Nov 23 '17 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. – Kent Chenery Nov 23 '17 at 9:56
  • @KentChenery I think you are right. I didn't read the question properly. I think they want optional parameters – ypercubeᵀᴹ Nov 23 '17 at 10:11
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
    ;
  • 2
    Nice. I was editing (with an almost identical query) while you answered. – ypercubeᵀᴹ Nov 23 '17 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 ...
 ;
  • 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. – Kent Chenery Nov 23 '17 at 10:02
  • @KentChenery thnx. I've added some explanation. Feel free to edit by the way. – ypercubeᵀᴹ Nov 23 '17 at 10:03
  • Sorry...I've changed my original comment - although the bits about when restrictions occurs is valid (and helpful). – Kent Chenery Nov 23 '17 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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