title: MySQL LEFT JOIN with GROUP BY and WHERE IN (sub query)
body: <p>I have one table with some statistics per date, which I want listed out with MySQL. For some dates there will be no statistics, so the result should look something like this:<br>
  2013-03-01: 3<br>
  2013-03-02: 2<br>
  2013-03-03: 0<br>
  2013-03-04: 1  </p>
  
  <p>I figured out that filling in the gaps with 0 -zero- could be solved with a separate table with all possible dates and LEFT JOIN. So far so good.</p>
  
  <p>The statistics (impressions) is in the table 'campaigndata':</p>
  
  <pre>id - int(11)
  date - date
  campaignid - int(11)
  impressions - int(11)</pre>
  
  <p>But I want to get only some of the statistics. To be more specific, I only want the rows from 'campaigndata' where 'campaignid' is in the table 'campaignfilter' with 'campaigntype' set to 1 (as an example).</p>
  
  <p>This is the table 'campaignfilter':</p>
  
  <pre>id - int(11)
  campaigntype - int(11)
  campaignid - int(11)</pre>
  
  <p>Anyone have clue how this could be done?</p>
  
  <p>PS: The structure of table 'campaigndata' is pretty much locked, since it is based on an automatic import from an external system.</p>
  
  <hr>
  
  <p><strong>SAMPLE RECORDS</strong></p>
  
  <pre><code>CREATE TABLE demo_campaigndata (
    id int(11) NOT NULL AUTO_INCREMENT,
    date date NOT NULL,
    campaignid int(11) NOT NULL,
    impressions int(11) NOT NULL,
    PRIMARY KEY (id)
  );
  INSERT INTO demo_campaigndata (id, date, campaignid, impressions) VALUES
  (1, '2013-03-03', 1, 100),
  (2, '2013-03-03', 2, 100),
  (3, '2013-03-03', 3, 100),
  (4, '2013-03-04', 2, 100),
  (5, '2013-03-05', 1, 100),
  (6, '2013-03-05', 2, 100);
  
  
  CREATE TABLE demo_campaignfilter (
    id int(11) NOT NULL AUTO_INCREMENT,
    campaigntype int(11) NOT NULL,
    campaignid int(11) NOT NULL,
    PRIMARY KEY (id)
  );
  INSERT INTO demo_campaignfilter (id, campaigntype, campaignid) VALUES
  (1, 1, 1),
  (2, 1, 3);
  
  
  CREATE TABLE demo_calendar (
    date date NOT NULL,
    PRIMARY KEY (date)
  );
  INSERT INTO demo_calendar (date) VALUES
  ('2013-03-01'),
  ('2013-03-02'),
  ('2013-03-03'),
  ('2013-03-04'),
  ('2013-03-05');
  </code></pre>
  
  <p><strong>DESIRED RESULT</strong></p>
  
  <pre><code>2013-03-01: 0
  2013-03-02: 0
  2013-03-03: 200
  2013-03-04: 0
  2013-03-05: 100
  </code></pre>