I am trying to get a Tag search working, and it is mostly working with the exception of LIKE Tag matches.
I have added example code to SQLFiddle to play with, and also included it here:
Tables and Data
CREATE TABLE Attendees
(
Id INT,
Text VARCHAR(500)
);
CREATE TABLE Tags
(
Id INT,
Description VARCHAR(50)
);
CREATE TABLE AttendeeTags
(
AttendeeId INT,
TagId INT,
Value VARCHAR(50)
);
INSERT INTO Attendees VALUES (1, 'Attendee 1');
INSERT INTO Attendees VALUES (2, 'Attendee 2');
INSERT INTO Tags VALUES (1, 'Tag Name 1');
INSERT INTO Tags VALUES (2, 'Tag Name 2');
INSERT INTO AttendeeTags VALUES (1, 1, 'Value 1');
INSERT INTO AttendeeTags VALUES (1, 1, 'Value 2');
INSERT INTO AttendeeTags VALUES (1, 2, 'Value 1');
INSERT INTO AttendeeTags VALUES (1, 2, 'Value 2');
INSERT INTO AttendeeTags VALUES (2, 1, 'Value 1');
Query
DECLARE @MandatoryTagXml XML
SET @MandatoryTagXml = '<tags><tag><description>Tag Name 1</description><value>Value 2</value></tag></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)
)
SELECT DISTINCT A.Id [AttendeeId]
FROM [dbo].[Attendees] A
INNER JOIN [dbo].[AttendeeTags] AT ON AT.AttendeeId = AttendeeId
INNER JOIN [dbo].[Tags] T ON T.Id = AT.TagId AND T.[Description] IN (SELECT [description] FROM MandatoryTags)
WHERE NOT EXISTS (
SELECT T.Id, c.value
FROM MandatoryTags c
JOIN Tags T
ON c.[description] = T.[Description]
-- Add LIKE match to value - This is the problem line
JOIN AttendeeTags AT
ON AT.Value LIKE '%' + C.[Value] + '%'
EXCEPT
SELECT ATT.TagId, ATT.Value
FROM [AttendeeTags] ATT
WHERE ATT.AttendeeId = A.Id
)
What I want is to get results when the Tags.Description matches exactly with the description in @MandatoryTagXml, and the AttendeeTags.Value is LIKE the Value supplied in @MandatoryTagXml
Without the following line things work as expected (i.e. when there is an exact match on the XML )
JOIN AttendeeTags AT ON AT.Value LIKE '%' + C.[Value] + '%'
But when I include it, I start getting incorrect results. For instance setting Value should return both Attendee.Id's, but it does not return any results.
I have tried various combinations of EXISTS and NOT EXISTS, and EXCEPT and INTERSECT etc, but cannot get it to work in all circumstances.
Can anyone offer any suggestions as to how to get it working?
An EXISTS join is a join in which the right side of the join needs to be probed only once for each outer row. Using such a definition, an EXISTS join does not literally use the EXISTS keyword.
EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation. In your example, the queries are semantically equivalent.
The WHERE NOT EXISTS() subquery will only return rows where the relationship is not met. However, if you did a LEFT OUTER JOIN and looked for IS NULL on the foreign key column in the WHERE clause, you can make equivalent behavior to the WHERE NOT EXISTS .
I think your join is more complicated than needed...
DECLARE @MandatoryTagXml XML
SET @MandatoryTagXml = '
<tags>
<tag><description>Tag Name 1</description><value>Value 1</value></tag>
<tag><description>Tag Name 2</description><value>Value 2</value></tag>
</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)
)
SELECT A.Id [AttendeeId]
FROM [dbo].[Attendees] A
INNER JOIN [dbo].[AttendeeTags] AT
INNER JOIN [dbo].[Tags] T
ON T.Id = AT.TagId
ON AT.AttendeeId = A.Id
INNER JOIN MandatoryTags m
ON T.Description = m.Description
AND AT.Value LIKE ('%' + m.Value + '%')
GROUP BY A.Id
-- Make sure that all of the tags are matched
HAVING COUNT(*) = (SELECT COUNT(*) FROM MandatoryTags)
Update: I've changed SQL to force matches on all tags in the xml.
In subquery replace C.value with AT.value, here is the link:
http://sqlfiddle.com/#!6/3d9e9/39
WHERE NOT EXISTS (
SELECT T.Id, AT.Value
FROM MandatoryTags c
JOIN Tags T
ON c.[description] = T.[Description]
-- Add LIKE match to value - This is the problem line
JOIN AttendeeTags AT
ON AT.Value LIKE '%' + C.[Value] + '%'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With