SQL SELECT where the value of the LIKE tag

Question

I'm trying to make a calendar service, within this calendar service, there are events, and events can be marked with metadata that can be searched.

I want to be able to search for entries where all tags (required tags) should exist and / or where tags (optional tags) exist.

I was able to create a query where this works when the tag value matches "exactly". But I can’t figure out how to return the results, where the value of the LIKE tag is "% 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 created an SQLFiddle Demo for it.

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

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

Can anyone suggest suggestions on how to get LIKE matches by tag values?

Many thanks

Answer 1

chucknelson gave me prod, I needed to figure out what to do.

Adding this section seems to match 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 perform a LIKE match and use the @SearchType parameter, I can either run the original query or make a corrected one.

Answer 2

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

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

First, I added a tag that looked like 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'); 

Then I created a search type flag / switch.

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

So, now you can switch the EXISTS join condition based on this flag. I changed NON-EXISTING to EXISTING just for my understanding. The following is a new join condition using the required tag block as an 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 refactoring and optimization in this SQL, but this should at least give you one idea on how to do LIKE matching, if necessary. Hope this helps!