Why am I getting a No Join Predicate when there is join criteria on all joins
LEFT OUTER JOIN [dbo].[int_organization] AS [Facilities] ON [Facilities].[organization_nm] = [Assignment].[FacilityName] AND [Facilities].[category_cd] = 'F'
The predicate [Facilities].[category_cd] = 'F' is pushed down to the index seek on the inner side of the join. It is independent of the join, so is not considered a join predicate.
The predicate [Facilities].[organization_nm] = [Assignment].[FacilityName] refers to a column defined in the CTE xv_DeviceSessionAssignment as:
CASE WHEN CHARINDEX('+', [InfoUnit].[Value]) > 0 THEN LEFT([InfoUnit].[Value], CHARINDEX('+', [InfoUnit].[Value]) - 1) ELSE NULL END AS [FacilityName]
After removing all the redundant joins and table accesses that are not needed to compute the simple final query:
SELECT DISTINCT [DLPAT].[patient_id] AS [PATIENTID] FROM [xv_PatientSessions] AS [DLPAT] INNER JOIN [dbo].[int_product_access] AS [Access] ON [Access].[organization_id] = [DLPAT].[UNIT_ID];
…the remaining join predicate is simplified and moved to the Filter to the left of the Hash Match Right Outer Join on the top branch of the query:
With both original join conditions moved elsewhere in the plan, the remaining join is logically a cross-join. This happens to result in a warning, which can be safely ignored.
This is quite normal, especially when running a simple query against a complex view (or CTE) where not all the operations in the views/CTEs are actually necessary to return correct and complete results.
Even so, when you see a missing join predicate warning, it is important to check that your query has proper join predicates, and that no errors have been made by the query writer resulting in a cross product where none was intended. Most often this will be quite clear from the results (which will be incorrect) and/or an unexpectedly long runtime for the query.
Please also note that (non-recursive) CTEs are nothing more, from a query optimization perspective, than an inline view definition. If you were to write your query as views (on views etc) instead of as CTEs (that reference other CTEs) the result and execution plan would most likely be identical.