title: MySQL: query joined to list of dates, but how do I display multiple entries per date
body: <p>I have a query that looks like (I've tried to strip out non-relevant fields/joins for clarity):</p>
  
  <pre><code>SET @num = -1;
  SELECT 
  *,
  CAST(DATE_ADD( '2012-04-01', interval @num := @num+1 day)AS DATE) AS date_sequence, 
  DAYOFWEEK(DATE_ADD('2012-04-01', interval @num+1 day)) AS day_week
  FROM batch AS b1
  
  left join (
  select
  batch.`startedDate` AS batch_startedDate,
  epiRun.`runType` AS epiRun_runType
  .... other fields selected.........
  from batch
  left join `epiRun` epiRun ON epiRun.`batchID`= batch.`keyID`
  .......other joins........
  WHERE batch.`startedDate` &gt;= '2012-04-01' AND batch.`startedDate` &lt;= '2012-04-18' 
  ORDER BY  batch.`startedDate`ASC) 
  AS b2 ON cast((b2.`batch_startedDate`) AS DATE)=CAST(DATE_ADD('2012-04-01', interval @num+1
  day)AS DATE)
  
  WHERE
  (DATE_ADD('2012-04-01', interval @num+1 day) &lt;= '2012-04-18') 
  </code></pre>
  
  <p>The nested select query performs as I expect when run by itself.  This query has a couple of problems:</p>
  
  <p>-Every field in from the batch table is selected, but since this is for an iReport it's not too much of a problem</p>
  
  <p>-I get the list of dates from 1st April to 18th April, but if I have multiple batches on a day then I only get one displayed - ideally I'd like multiple identical entries in the date column with a unique entry for each batch.  It is important that I can see when there are days with no batches.</p>
  
  <p>Example of table I have:</p>
  
  <pre><code>Date          Batch
  01/04/2012    TS01
  02/04/2012    TS03
  03/04/2012    null
  </code></pre>
  
  <p>and what I'd like to generate:</p>
  
  <pre><code>Date          Batch
  01/04/2012    TS01
  01/04/2012    TS02
  02/04/2012    TS03
  02/04/2012    TS04
  03/04/2012    null
  </code></pre>