title: MySQL JOIN Not returning correct row set
body: <p>I'm having an issue with a join query which I am running. For some reason I am only getting 59,596 rows returned instead of 68,058.</p>
  
  <p>I have tried various LEFT / RIGHT / INNER JOINS with no luck. I have double checked table CC and can confirm that there are 68,059 rows of data for the specified range. The derived table XX, however, has fewer rows.</p>
  
  <p>I want to return all the rows from table CC despite if the row is in Table XX or not.</p>
  
  <p>Below is the query I am using as well as the table structure:</p>
  
  <pre><code>    SELECT 
  
          CC.entity_code 'Entity Code 2'    
  
      ,       (SELECT MAX(action_date) AS secMaxDate 
              FROM 
              sys_reference.entities_visited DD
  
              WHERE 
              entity_code = XX.storeCode 
              AND     action_date &lt; XX.maxDate) ' Previous Visit Date'
      ,       XX.maxDate  'Latest Visit Date'
      ,       cycle_end_date 'Cycle End Date'
      ,       WEEK(cycle_end_date,1)  'Cycle Week'
      ,   CASE 
  
              WHEN XX.visits &gt; 10 THEN 0
  
              ELSE XX.visits
  
          END 'Total Visits (28 Days)'
      ,   CASE 
              WHEN XX.maxDate = MAX(CC.action_date) THEN 1
              ELSE 0
          END AS 'Call Cycle Compliance' 
      FROM 
          sys_reference.call_cycles CC
      LEFT JOIN        
          (SELECT 
              BB.entity_code AS storeCode
          ,   MAX(action_date) AS maxDate
          ,   MAX(cycle_end_date) AS cycle_end_date
          ,   (SELECT COUNT(AA.entity_code) FROM sys_reference.entities_visited AA WHERE AA.action_date BETWEEN '2017-01-01' AND '2017-01-31' AND AA.entity_code = BB.entity_code) AS visits
          FROM 
              sys_reference.entities_visited BB
  GROUP BY 
              BB.entity_code
          ,   WEEK(BB.action_date,1)) AS XX        
      ON  
          CC.entity_code = XX.storeCode
      WHERE
          CC.action_date BETWEEN '2017-01-01' AND '2017-01-31'
      AND     YEARWEEK(cycle_end_date,1) BETWEEN YEARWEEK('2017-01-01') AND YEARWEEK('2017-01-31')
      GROUP BY 
          CC.entity_code
      ,   WEEK(cycle_end_date,1)
      LIMIT 0,100000;
  </code></pre>
  
  <p>Call Cycles Table</p>
  
  <pre><code>entity_code | action_date| cycle_end_date   
  ------------+------------+--------------- 
  108792      |2017-01-07  |2017-01-11    
  108792      |2017-01-10  |2017-01-11  
  108792      |2017-01-12  |2017-01-18  
  108793      |2017-01-08  |2017-01-11    
  108795      |2017-01-06  |2017-01-11    
  108796      |2017-01-05  |2017-01-11    
  108795      |2017-01-13  |2017-01-18  
  108792      |2017-01-07  |2017-01-11   
  108792      |2017-01-14  |2017-01-18    
  108793      |2017-01-14  |2017-01-18    
  108796      |2017-01-16  |2017-01-18
  </code></pre>
  
  <p>Entities Visited Table</p>
  
  <pre><code>entity_code | visit_date | cycle_end_date   
  ------------+------------+--------------- 
  108792      |2017-01-07  |2017-01-11    
  108793      |2017-01-08  |2017-01-11    
  108795      |2017-01-06  |2017-01-11    
  108796      |2017-01-05  |2017-01-11    
  108795      |2017-01-13  |2017-01-18    
  108792      |2017-01-14  |2017-01-18    
  108793      |2017-01-14  |2017-01-18    
  108796      |2017-01-16  |2017-01-18
  </code></pre>
  
  <p>From the sample data, I need to return all rows from the Call Cycle Table.</p>