title: MySQL Combined sales report
body: <p>I have 2 tables: "prueba" is the catalogue with all the descriptions and codes for the products.</p>
  
  <pre><code>Field   Type    Null    Key Default Extra
  cod varchar(10) NO  PRI     
  nom varchar(100)    NO          
  tip varchar(50) NO          
  des varchar(500)    NO          
  pre double  NO          
  can int(11) YES     1   
  cla varchar(3)  NO          
  fcm varchar(20) YES         
  cmb char(1) NO      0   
  </code></pre>
  
  <p>And "venta" contains the sales:</p>
  
  <pre><code>Field   Type    Null    Key Default Extra
  id  int(255)    NO  PRI     auto_increment
  can int(11) NO          
  tot double  NO          
  fec varchar(30) NO          
  hor varchar(20) NO          
  cod_f   varchar(10) YES MUL     
  per_f   varchar(30) YES MUL     
  tdt varchar(15) NO          
  des double  YES         
  com varchar(500)    YES         
  cdv varchar(15) NO          
  cliente varchar(100)    YES         
  fdc varchar(30) YES         
  local   varchar(25) NO          
  trans   varchar(10) YES         
  </code></pre>
  
  <p>I need to do a report that shows how much of certain code was sold on certain shop. So, in "venta" cod_f is the code, can is the quantity sold, tdt indicates the type of the transaction and local is where was the transaction made. So if the transaction was a sale, tdt = 'V'. The following query shows me only the codes of the transactions made on that store.</p>
  
  <pre><code>select prueba.cod, ifnull(sum(venta.can),0)
  from venta, prueba
  where 
  venta.cod_f = prueba.cod and 
  tdt = 'V' and
  local = 'XCSUR' and
  yearweek( DATE_FORMAT(STR_TO_DATE(fec, '%d/%m/%Y'), '%Y-%m-%d') ) between yearweek(curdate() - interval 8 week) and yearweek(curdate())
  group by cod_f
  order by CHAR_LENGTH(cod_f), cod_f;
  </code></pre>
  
  <p>What I want is to show even the codes that are not part of the sales showing them as 0, F/E:</p>
  
  <p>My query shows:</p>
  
  <pre><code>2010    1
  2012    5
  2013    2
  2022    1
  </code></pre>
  
  <p>And omits the inbetween codes. What I want is something like:</p>
  
  <pre><code>2010    1
  2011    0
  2012    5
  2013    2
  2014    0
  2022    1
  </code></pre>
  
  <p>Thanks in advance.</p>