0
SELECT
    *
--  cd.CountId,
--  cd.ApproachId,
--  c .CountId,
--  c .IntersectionId,
--  l .IntersectionId,
--  l .ApproachId
FROM
    dbo.CountData AS cd

    INNER JOIN dbo."Count" AS c ON
        cd.CountId = c.CountId

    LEFT JOIN dbo.Leg AS l ON
        c.IntersectionId = l.IntersectionId
        AND
        cd.ApproachId = l.ApproachId 

WHERE
    l.IntersectionId IS NULL

How can I rewrite this query such that no LEFT JOIN is involved?

The end goal is to have a query that is acceptable as an indexed view (as indexed views cannot use LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, nor subqueries and derived-tables).

Dai
  • 141,631
  • 28
  • 261
  • 374
CuriousDeveloper
  • 849
  • 2
  • 8
  • 27
  • and why do you want an indexed view? – Mitch Wheat Aug 14 '18 at 15:50
  • 1
    because I want to write a complicated constraint https://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/ that uses the non-existance of a value to enforce a violation of the uniqueness – CuriousDeveloper Aug 14 '18 at 15:53
  • 2
    something tells me you are solving this the wrong way. – Mitch Wheat Aug 14 '18 at 15:55
  • 1
    FYI, you can make a function that can be used as a constraint. What version of SQL Server are you using? – UnhandledExcepSean Aug 14 '18 at 15:55
  • This has all the tell tale signs of being an xy problem. http://xyproblem.info/ – Sean Lange Aug 14 '18 at 16:19
  • Have a look at this: http://www.sqlservercentral.com/articles/Indexing/indexedviewswithouterjoins/1884/ – Alan Burstein Aug 14 '18 at 17:10
  • 1
    @UnhandledExcepSean using functions in a constraint is inherently prone to many issues. I found issues where the query optimizer will ignore a constraint when a function is used. The linked sql constraint via unique indexed views is AFAIK more or less how FKs work and aren't prone to all the issues of functions – CuriousDeveloper Aug 14 '18 at 17:33
  • 2
    @CuriousDeveloper I've not seen a problem with a well written function in a constraint. Perhaps you can expand on why a function can't work in your question. – UnhandledExcepSean Aug 14 '18 at 17:36
  • The function may work but can stop working when the query optimizer may find a new way to optimize a query. Using functions are also quite slow. One example of this behavior is https://stackoverflow.com/questions/33054748/check-constraint-does-not-work-on-bulk-insert-for-more-than-250-records and there are numerous articles about avoiding functions in constraints. I avoid function constants all together now and try to use FKs when I can in the design, in this case I cant use a FK because of multiple tables – CuriousDeveloper Aug 14 '18 at 17:39

0 Answers0