JOIN ON LIKE issue WHERE EXISTS in SQL server

Question

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?

Answer 1

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.

Answer 2

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] + '%'