I have this fairly straight-forward snippet of a query:
DECLARE @ClientGUID numeric(16) = 9000011916900200; SELECT ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN FROM Client c INNER JOIN ClientID mrn ON mrn.ClientGUID = c.GUID AND mrn.TypeCode = 'MRN1' INNER JOIN Chart ch ON ch.ClientGUID = c.GUID WHERE c.GUID = @ClientGUID
This throws a "No Join Predicate" warning on the join between Chart and Client because the optimizer pushes the variable into the index seek predicate leaving nothing to join on and essentially doing a CROSS JOIN after the index seek. I get that this is a good thing.
My question is, if in six months someone sees that warning and starts freaking out, how can i tell the optimizer that this okay (intended behavior) and not to throw the warning?
I've tried changing the INNER JOIN to a LEFT JOIN (which I saw on another question about this warning), CROSS APPLY, and an explicit CROSS JOIN (and adding the condition in the WHERE clause) -- none of them get rid of the blasted warning.
I see this issue on both
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1765.0 (X64) Enterprise Edition (64-bit) Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) Enterprise Edition: Core-based Licensing (64-bit)