T-SQL - How to write a complex conditional join including a many-to-many join

Question

I have tried solving this problem by posting other related questions here that focused on parts of the query. However I might as well post the entire thing and see if anyone can help. I have the following tables with the following fields:

tblPerson - PersonID, PersonName
tblGroup - GroupID, Name
tblGroupMembership - PersonID, GroupID
tblPersonCities - CityID, PersonID, City

It's a pretty simple setup. We have Person and Group and the GroupMembership is a many to many join between the two. And then People can have multiple Cities.

What I am trying to do is write a stored procedure for searching this data based on a few different parameters. The stored procedure will only return Person information. The stored procedure should take 3 parameters:

@PersonName - a person's name or part of a person's name
@GroupIDList - a comma delimited list of GroupIDs
@City - a city name or part of a city name

I'd like for the stored procedure to be able to not require values for any of the parameters. So if all of the parameters were NULL then it should return all of the Person records. If a list of GroupIDs is passed in then it should only return Person records that match ALL of the Groups in the passed in list. I hope I have explained this correctly. I know this is a long question but I can't explain it any other way. I have some code that ALMOST works. The only problem is that it doesn't seem to work if all of the parameters are NULL. (And I haven't figured out how to bring in the City) Here is my code sample. (NOTE: fnSplit is a custom function that takes a comma delimited string and returns a table with the different values)

declare @name varchar(50)
declare @city varchar(50)
declare @grouplist varchar(50)

set @name = null
set @city = null
set @grouplist = null

select distinct
p.PersonID,
p.PersonName,
c.City
from
tblPerson p left join tblCities c on p.PersonID = c.PersonID
join
 (
    select m.PersonID
    from tblGroupMembership m
    where (m.GroupID in (select item from fnSplit(@grouplist, ',')))
    group by m.PersonID
    having (count(*) = (select count(*) from fnSplit(@grouplist, ',')))
 ) as filter
 on (@grouplist is not null) and (p.PersonID = filter.PersonID)
where
((@name is null) or (p.PersonName like '%' + @name + '%')) and
((@city is null) or (c.City like '%' + @city + '%'))

Answer 1

When you say "any parameter" is null, does this include @grouplist as well?

If so, it may be because you are doing an inner join to the filter table. If @grouplist is null, then there will be no rows to meet that join as I'm assuming that fnSplit will return no rows and so the in statement will never be true. I'm just eyeballing it at this point...

Answer 2

I lean towards using dynamic sql in these situations, because using ORs to accommodate this logic are horrible for performance and sargability. The following example is intended for SQL Server 2005+:

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'SELECT DISTINCT
                   p.personid,
                   p.personname,
                   c.city
              FROM TBLPERSON p
         LEFT JOIN TBLCITIES c ON c.personid = p.personid '

SET @SQL = @SQL + CASE 
                    WHEN @grouplist IS NOT NULL THEN
                      ' JOIN (SELECT m.PersonID
                                FROM TBLGROUPMEMBERSHIP m
                               WHERE m.GroupID IN (SELECT item FROM fnSplit(@grouplist, ',')))
                            GROUP BY m.PersonID
                              HAVING COUNT(*) = (SELECT COUNT(*) FROM fnSplit(@grouplist, ',')))) g ON g.personid = p.personid '
                    ELSE
                      ' '
                  END

  SET @SQL = @SQL + ' WHERE 1 = 1 ' --trick to make contatentating WHERE clause easier

IF @name IS NOT NULL
  SET @SQL = @SQL + ' AND p.personname LIKE '%' + @name + '% '

IF @city IS NOT NULL
  SET @SQL = @SQL + ' AND c.city LIKE '%' + @city + '% '  

BEGIN

  EXEC sp_executesql @SQL N'@grouplist varchar(50), @grouplist varchar(50), @name varchar(50), @city varchar(50)',
                      @grouplist, @grouplist, @name, @city

END

Mind that sp_executesql will cache the query plan - per The curse and blessings of dynamic SQL.

Answer 3

try this:

First is a function to turn the comma delimited list of GroupIds into a table variable...

CREATE FUNCTION [dbo].[ParseString] (@S Text, @delim VarChar(5))
Returns @tOut Table 
    (ValNum Integer Identity Primary Key, 
     sVal VarChar(8000))
As
Begin 
Declare @dLLen TinyInt   -- Length of delimiter
Declare @sWin  VarChar(8000)-- Will Contain Window into text string
Declare @wLen  Integer   -- Length of Window
Declare @wLast TinyInt   -- Boolean to indicate processing Last Window
Declare @wPos  Integer   -- Start Position of Window within Text String
Declare @sVal  VarChar(8000)-- String Data to insert into output Table
Declare @BtchSiz Integer    -- Maximum Size of Window
    Set @BtchSiz = 7900  -- (Reset to smaller values to test routine)
Declare @dPos Integer    -- Position within Window of next Delimiter
Declare @Strt Integer    -- Start Position of each data value within Window
-- -------------------------------------------------------------------------

    -- ---------------------------
    If @delim is Null Set @delim = '|'
    If DataLength(@S) = 0 Or
     Substring(@S, 1, @BtchSiz) = @delim Return
    -- ---------------------------
    Select @dLLen = Len(@delim),
     @Strt = 1, @wPos = 1,
     @sWin = Substring(@S, 1, @BtchSiz)
    Select @wLen = Len(@sWin),
       @wLast = Case When Len(@sWin) = @BtchSiz
        Then 0 Else 1 End,
       @dPos = CharIndex(@delim, @sWin, @Strt)
    -- ----------------------------
    While @Strt <= @wLen
     Begin
      If @dPos = 0 Begin    -- No More delimiters in window
       If @wLast = 1 Set @dPos = @wLen + 1 
       Else Begin
        Set @wPos = @wPos + @Strt - 1
        Set @sWin = Substring(@S, @wPos, @BtchSiz)
        -- ----------------------------------------
        Select @wLen = Len(@sWin), @Strt = 1,
            @wLast = Case When Len(@sWin) = @BtchSiz
            Then 0 Else 1 End,
            @dPos = CharIndex(@delim, @sWin, 1)
        If @dPos = 0 Set @dPos = @wLen + 1 
       End
      End
      -- -------------------------------
      Set @sVal = LTrim(Substring(@sWin, @Strt, @dPos - @Strt))
      Insert @tOut (sVal) Values (@sVal)
      -- -------------------------------
      -- Move @Strt to char after last delimiter
      Set @Strt = @dPos + @dLLen 
      Set @dPos = CharIndex(@delim, @sWin, @Strt)
     End
    Return
End

Then here's the stored procedure

  Create Procedure GetPersons
  @PersonName varChar(50) = nULL,
  @City varChar(50) = Null,
  @GroupIDList varChar(5000)
  As 
  Set NoCOunt On

        Declare @Groups Table (GId Integer Primary Key Not Null)
        If Len(@GroupIDList) = 0
           Insert @Groups(GId)
           Select GroupId From tblGroup
        Else
           Insert @Groups(GId)
           Select Cast(sVal as Integer)
           From dbo.ParseString(@GroupIDList, ',')

        Select PersonId, PersonName
        From tblPerson p 
        Where Exists (Select * From tblGroupMembership gm 
                         Join @Groups g On g.GId = gm.GroupId
                      Where PersonId = p.PersonId)
          And Exists (Select * From tblPersonCities 
                      Where PersonId = p.PersonId
                         And City =  IsNull(@City, City))