IN statement with SqlDependency not working
Does sqldependency work with a statement IN
? My team:
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 looked at MSDN Dodumentation but didn't mention anything about the IN operator.
Thanks in advance.
source to share
From the documentation:
"The operator must not contain subqueries, outer joins, or self-joins."
IN usually displays a semi-join. Damien_The_Unbeliever notes that:
IN () is defined as a subquery or a sequence of expressions - the one in OPs' question is a subquery.
Therefore, it is probably not supported.
Can you express IN as an inner join? This preserves semantics if the IN query produces at most one row for each outer row. If there is more, the lines will be duplicated. However, this does not matter for the notification. It should work under the same circumstances.
source to share