SQL SELECT where tag value LIKE

Question

I am attempting to make a Calendar service, within that calendar service, there are events, and events can be tagged with metadata which is searchable.

I want to be able to search for records where all tags must exists (Mandatory Tags) and/or where any tags exist (Optional Tags).

I have managed to create a query where this works, when the tag value matches 'exactly'. But I cannot work out how to return results where the tag value is LIKE '%value%'.

Here is my current implementation

Tables And Data

CREATE TABLE Events
(
 Id INT,
 EventText VARCHAR(500)
);

CREATE TABLE EventDates
(
 Id INT,
 EventId INT,
 StartDate DATETIME,
 EndDate DATETIME,
 Archived BIT
);

CREATE TABLE Tags
(
 Id INT,
 Description VARCHAR(50)
);

CREATE TABLE EventTags
(
 EventId INT,
 TagId INT,
 Value VARCHAR(50)
);

INSERT INTO Events VALUES (1, 'Event Name 1');
INSERT INTO Events VALUES (2, 'Event Name 2');

INSERT INTO EventDates VALUES (1, 1, '2013-01-01', '2013-01-02', 0);
INSERT INTO EventDates VALUES (2, 1, '2013-01-07', '2013-01-08', 0);
INSERT INTO EventDates VALUES (3, 2, '2013-01-02', '2013-01-03', 0);

INSERT INTO Tags VALUES (1, 'Tag Name 1');
INSERT INTO Tags VALUES (2, 'Tag Name 2');

INSERT INTO EventTags VALUES (1, 1, 'Value 1');
INSERT INTO EventTags VALUES (1, 1, 'Value 2');
INSERT INTO EventTags VALUES (1, 2, 'Value 1');
INSERT INTO EventTags VALUES (1, 2, 'Value 2');
INSERT INTO EventTags VALUES (2, 1, 'Value 1');

Query

DECLARE @MandatoryTagXml XML
DECLARE @OptionalTagXml XML
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @SearchTypeId SMALLINT

SET @StartDate = '2013-01-01'
SET @EndDate = '2013-01-31'
SET @SearchTypeId = 1

-- Tags that it must match all of
SET @MandatoryTagXml = '<tags>
                          <tag>
                            <description>Tag Name 1</description>
                            <value>Value 1</value>
                          </tag>
                        </tags>'

-- Tags that it can match one or more of
SET @OptionalTagXml = '<tags>
                          <tag>
                            <description>Tag Name 2</description>
                            <value>Value 2</value>
                          </tag>
                        </tags>'

    DECLARE @MandatoryIdTable TABLE ([EventId] BIGINT, [EventDateId] BIGINT)
    DECLARE @OptionalIdTable TABLE ([EventId] BIGINT, [EventDateId] BIGINT)

IF(@MandatoryTagXml IS NOT NULL)
BEGIN
    -- Select ids with matching mandatory tags.
    ;WITH MandatoryTags AS
     (
      SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
             TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
      FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
      )

    INSERT INTO @MandatoryIdTable
    -- Records where ALL tags match EXACTLY
    SELECT E.Id [EventId], ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
    AND NOT EXISTS (
                    SELECT T.Id, c.value
                    FROM MandatoryTags c JOIN Tags T
                        ON c.[description] = T.[Description]
                    EXCEPT
                    SELECT T.TagId, T.Value
                    FROM [EventTags] T
                    WHERE T.EventId = E.Id                          
                    )
END
ELSE -- Select All records 
BEGIN
    INSERT INTO @MandatoryIdTable
    -- Records where ALL tags match EXACTLY
    SELECT E.Id [EventId], ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
END

    ;WITH OptionalTags AS
     (
      SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
             TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
      FROM @OptionalTagXml.nodes('/tags/tag') AS T(TagValue)
      )

    INSERT INTO @OptionalIdTable
    -- Records ANY tags match EXACTLY
    SELECT E.Id [EventId], ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
    AND EXISTS (
                SELECT T.Id, c.value
                FROM OptionalTags c JOIN Tags T
                    ON c.[description] = T.[Description]
                INTERSECT
                SELECT T.TagId, T.Value
                FROM [EventTags] T
                WHERE T.EventId = E.Id                          
                )

-- Determine if we need to factor in optional tags in result set
IF (@OptionalTagXml IS NOT NULL)
BEGIN
    -- Select results that exist in both optional and mandatory tables
    SELECT DISTINCT M.*
    FROM @MandatoryIdTable M
    INNER JOIN @OptionalIdTable O ON O.EventId = M.EventId AND O.EventDateId = M.EventDateId
END
ELSE
BEGIN
    -- Select results that exist in mandatory table
    SELECT DISTINCT M.*
    FROM @MandatoryIdTable M
END

I have created an SQLFiddle Demo for it.

My idea is to use @SearchTypeId to switch between exact match searching and LIKE match searching.

(Note I am not a DBA, so there may be better ways to do this. I am open to suggestions)

Can anyone offer suggestions as to how to get LIKE matches on tag values?

Many thanks

Answer 1

chucknelson gave me the prod I needed to spot what needed to be done.

Adding this section gets a like match on the value:

    JOIN EventTags ET
          ON C.[Value] LIKE '%' + ET.Value + '%'

So, for example, the mandatory section becomes:

    -- Select ids with matching mandatory tags.
    ;WITH MandatoryTags AS
     (
      SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
             TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
      FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
      )

    INSERT INTO @MandatoryIdTable
    -- Records where ALL tags match EXACTLY
    SELECT E.Id [EventId], ED.Id [EventDateId]
    FROM [dbo].[Events] E
    INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
    WHERE ED.StartDate >= @StartDate
    AND ED.EndDate <= @EndDate
    AND ED.Archived = 0
    AND NOT EXISTS (
                    SELECT T.Id, c.value
                    FROM MandatoryTags c 
                    JOIN Tags T 
                        ON c.[description] = T.[Description]
-- Add LIKE match on value
                    JOIN EventTags ET
                        ON C.[Value] LIKE '%' + ET.Value + '%'
                    EXCEPT
                    SELECT T.TagId, T.Value
                    FROM [EventTags] T
                    WHERE T.EventId = E.Id
                    )

This allows me to do the LIKE match, and using a @SearchType parameter, I can either run the original query, or this amended one accordingly.

Answer 2

I think your idea of using some type of flag/switch to change the matching type will work. I implemented it using words instead of IDs, but if you just toggle the join condition based on the search type, you should get LIKE matching as expected.

Fiddle: http://sqlfiddle.com/#!3/d9fbd/3/0

I first added a tag that was similar to tag 1 and attached it to event 2 for testing.

INSERT INTO Tags VALUES (3, 'Different Tag Name 1');
INSERT INTO EventTags VALUES (2, 3, 'Value 3');

I then created the search type flag/switch.

DECLARE @SearchType NVARCHAR(10)
SET @SearchType = 'LIKE' --other type is EXACT

So now you can toggle the EXISTS join condition based on that flag. I changed your NOT EXISTS to EXISTS just for my understanding. Below is the new join condition, using the mandatory tag block as the example.

    -- Select ids with matching mandatory tags.
;WITH MandatoryTags AS
 (
  SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
         TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
  FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
  )

INSERT INTO @MandatoryIdTable
-- Records where ALL tags match EXACTLY or LIKE
SELECT E.Id [EventId], ED.Id [EventDateId]
FROM [dbo].[Events] E
INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
WHERE ED.StartDate >= @StartDate
AND ED.EndDate <= @EndDate
AND ED.Archived = 0
AND EXISTS (
                -- Just care about tag IDs here, not the values
                SELECT T.Id
                FROM MandatoryTags c JOIN Tags T
                    ON (
                      -- Toggle join type based on flag/switch
                      (@SearchType = 'EXACT' AND c.[description] = T.[Description])
                      OR
                      (@SearchType = 'LIKE' AND T.[Description] LIKE ('%' + c.[description] + '%'))
                    )
                INTERSECT
                SELECT T.TagId
                FROM [EventTags] T
                WHERE T.EventId = E.Id                          
                )

I'm sure there is some re-factoring and optimization you can do in this SQL, but this should at least give you one idea on how to do LIKE matching if desired. Hope it helps!