title: Mysql union, left join, group and count
body: <p>Heres my query</p>
  
  <pre><code> SELECT 
      fsi_courier_assignment_print_master_listing.master_listing_id,
      fsi_master_listing.transmittal_id,
      fsi_transmittals.product_name,
      fsi_transmittals.transmittal_id
   FROM fsi_courier_assignment_print_master_listing
   LEFT JOIN fsi_master_listing ON fsi_courier_assignment_print_master_listing.master_listing_id = fsi_master_listing.master_listing_id
   LEFT JOIN fsi_transmittals ON fsi_master_listing.transmittal_id = fsi_transmittals.transmittal_id
   WHERE dispatch_code_id=".$this-&gt;db-&gt;escape($dispatch_code_id)."
   UNION ALL
   SELECT 
      fsi_courier_assignment_print_master_listing_undelivered.master_listing_id,
      fsi_master_listing.transmittal_id,
      fsi_transmittals.product_name,
      fsi_transmittals.transmittal_id
   FROM fsi_courier_assignment_print_master_listing_undelivered
   LEFT JOIN fsi_master_listing ON fsi_courier_assignment_print_master_listing_undelivered.master_listing_id = fsi_master_listing.master_listing_id
   LEFT JOIN fsi_transmittals ON fsi_master_listing.transmittal_id = fsi_transmittals.transmittal_id
   WHERE dispatch_code_id=".$this-&gt;db-&gt;escape($dispatch_code_id)."
  </code></pre>
  
  <p><code>fsi_courier_assignment_print_master_listing</code> table</p>
  
  <pre><code>  master_listing_id   dispatch_code_id
            2                 2
            5                 2
            36                2
            37                2
            134               2
            135               2
            136               2
            137               2
            138               2
            139               2
            140               2
  </code></pre>
  
  <p><code>fsi_courier_assignment_print_master_listing_undelivered</code> table</p>
  
  <pre><code>  master_listing_id   dispatch_code_id
            1                 2
  </code></pre>
  
  <p><code>fsi_master_listing</code> table</p>
  
  <pre><code>  master_listing_id   transmittal_id
            1                 1
            2                 1
            5                 2
            36                2
            37                2
            134               3
            135               3
            136               3
            137               3
            138               3
            139               3
            140               3
  </code></pre>
  
  <p><code>fsi_transmittals</code> table</p>
  
  <pre><code>  transmittal_id   product_name
          1             Name 1
          2             Name 2
          3             Name 3
  </code></pre>
  
  <p>What Im trying to do is to get the combined result of product from <code>fsi_courier_assignment_print_master_listing</code> and <code>fsi_courier_assignment_print_master_listing_undelivered</code> where dispatch_code_id='2' and count them</p>
  
  <p>My desire Output would be</p>
  
  <pre><code>  Product Name    Product Count
       Name 1            2
       Name 2            3
       Name 3            7
  </code></pre>
  
  <p>Thanks in advance, hope somebody can help me to this..</p>