Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JOIN ON LIKE issue WHERE EXISTS in SQL server

Tags:

sql

sql-server

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?

like image 845
Yetiish Avatar asked Sep 09 '13 14:09

Yetiish


People also ask

Can we use EXISTS with join in SQL?

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.

Why use EXISTS instead of join?

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.

WHERE Not EXISTS SQL join?

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 .


2 Answers

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.

like image 52
David Avatar answered Nov 05 '22 19:11

David


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] + '%'
like image 24
Sonam Avatar answered Nov 05 '22 19:11

Sonam