3

Does sqldependency work with IN operator? My Command is:

SELECT n.PublishedAt
FROM dbo.Navigations n
WHERE NavigationGroupId IN (SELECT ng.Id FROM dbo.NavigationGroups ng
                            WHERE ng.SiteId = 1
                              AND ng.IsActive = 1
                              AND ng.[Type]= 'Secondary')
  AND n.IsActive = 1

I had a look to MSDN Dodumentation but nothing mentioned about IN operator.

Thanks in advance.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Rana
  • 1,170
  • 3
  • 14
  • 28
  • 2
    "The statement must not contain subqueries, outer joins, or self-joins." Hm, IN usually maps to a semi-join. Probably not supported. Can you express the IN as an inner join? – usr Jun 22 '15 at 10:29
  • @usr - you've nailed it more directly - [`IN()`](https://msdn.microsoft.com/en-GB/library/ms177682.aspx) is defined as either a subquery or a sequence of expressions - the one in the OPs question s a subquery. And your quote covers the rest. – Damien_The_Unbeliever Jun 22 '15 at 10:32
  • @usr, I think you should convert your comment into an answer, so it can be upvoted. As someone else will hit this issue at some point. – Ian Ringrose Jun 22 '15 at 10:37

1 Answers1

3

From the documentation:

"The statement must not contain subqueries, outer joins, or self-joins."

IN usually maps to a semi-join. Damien_The_Unbeliever notes, that:

IN() is defined as either a subquery or a sequence of expressions - the one in the OPs question s a subquery.

Therefore it probably is not supported.

Can you express the IN as an inner join? This preserves semantics if the IN query produces at most one row for each outer row. If there are more then rows will be duplicated. That does not matter for the notification, though. It should fire under exactly the same circumstances.

usr
  • 168,620
  • 35
  • 240
  • 369