title: Join works in query but not in view-script
body: <p>I have a query with a join that works fine executing it directly (so without the &quot;create view&quot; part). For certain rows I get an averageDuration from the subquery T3. But when I build a view with this exact same query and Select all rows from this view, the join is not working. I get NULL as averageDuration where there was a value, executing the query directly. How is this possible?</p>
  <p>The code uses views. Every tablename starting with &quot;V_&quot; is actually a view.
  To get this script working as a view, I need to change the join with V_Operationroom in subquery T3, to a normal table instead of the view. Strange thing about this solution is that the fields (Operationroom_Skey and IndOperationroom) used from the table or view (V_)Operationroom have the same datatype in table and view. The view actually gets them from the table (<code>SELECT Operationroom_Skey, IndOperationroom FROM Operationroom</code>).</p>
  <pre><code>CREATE VIEW [dbo].[V_WaitingList]
  AS
      
  SELECT  T1.Operationnumber, T3.AverageDuration
  
  FROM V_Operation T1                                                                     
      
      LEFT JOIN V_Operationcode T2    ON  T1.Operationcode_Skey = T2.Operationcode_Skey                                                                       
  
      LEFT JOIN   (
                      SELECT  
                          S5.Operationcode,
                          AVG(S1.duration) AS AverageDuration
                      FROM V_F_Operatie S1
                          LEFT JOIN V_Operation S2 ON S1.Operation_SKey = S2.Operation_SKey
                          LEFT JOIN V_Operationroom S3 ON S1.Operationroom_Skey = S3.Operationroom_Skey
                          LEFT JOIN V_Operationcode S5 ON S2.Operationcode_Skey = S5.Operationcode_Skey   
                      WHERE   
                          S5.Operationcode IS NOT NULL AND
                          S3.IndOperationroom = '1'
                      GROUP BY    
                          S5.Operatieverrichtingcode
  
      )   T3  ON  T3.Operationcode = T2.Operationcode
  
  GO
  </code></pre>