title: adding store id to product export in opencart (mysql query)
body: <p>Another weird question that requires a bit of explanation, thanks in advance for the help.</p>
  
  <p>I've inherited an opencart installation and I have been asked to modify a report generator (Product Export Express) which runs a big MySQL query to spit out a xls file of all the pertinent product information along with other tidbits of data.  Right now I am using the below code:</p>
  
  <pre><code>    &lt;?php
  ini_set("memory_limit","1G");   
  class ModelInventoryExpress extends Model
  {
  
  
      public function getProducts(){
          $products_sql = "SELECT
          p.product_id AS             `Product ID`, 
          p.model AS                  `Model`,
          p.sku as                    `SKU`,
          pd.name AS                  `Product Name`,
          cd.name as                  `Category`,
          p.location AS               `Location`,
          p.quantity AS               `Quanity`,
          (SELECT `text` 
          FROM product_attribute pa 
          WHERE pa.product_id = p.product_id 
          AND pa.attribute_id = 6) AS `Box Count`,
  
          (SELECT `text` 
          FROM product_attribute pa 
          WHERE pa.product_id = p.product_id 
          AND pa.attribute_id = 7) AS `Length`,
  
          (SELECT `text` 
          FROM product_attribute pa 
          WHERE pa.product_id = p.product_id 
          AND pa.attribute_id = 5) AS `Ring Gauge`,
  
          (SELECT `text` 
          FROM product_attribute pa 
          WHERE pa.product_id = p.product_id 
          AND pa.attribute_id = 4) AS `Strength`,
  
          (SELECT `text` 
          FROM product_attribute pa 
          WHERE pa.product_id = p.product_id 
          AND pa.attribute_id = 3) AS `Wrapper`,
  
          CASE
          WHEN p.`status` = 1
          THEN 'active'
          ELSE 'non-active'
          END  AS `Status` ,
          md.name as                  `Manufacturer`,
          FORMAT(p.price, 2) as   `Price`,
          FORMAT(p.cost, 2) as    `Cost`,
          FORMAT(p.wholesale, 2) as   `Wholesale`,                
  
          CASE
          WHEN pts.`store_id` = 0
          THEN 'StogieBoys.com'
          WHEN pts.`store_id` = 1
          THEN 'CigarHeist.com'
          WHEN pts.`store_id` = 2
          THEN 'm.stogieboys.com'
          WHEN pts.`store_id` = 3
          THEN 'BestCigarStuff.com'
          WHEN pts.`store_id` = 6
          THEN 'SBCigarWholesale.com'
          WHEN pts.`store_id` = 8
          THEN 'StogieTrade.com'
          ELSE 'None'
          END  AS                         `Store`,
  
          CASE
          WHEN p.`is_dropshipped` = 1
          THEN 'Yes'
          ELSE 'No'
          END  AS                         `Is Dropshipped`        
  
          FROM
          product p,
          product_description pd,
          product_to_category ptc,
          category_description cd,
          manufacturer md,
          product_to_store pts
          WHERE 1
          AND p.product_id = pd.product_id
          AND p.product_id = ptc.product_id
          AND ptc.category_id = cd.category_id
          AND p.manufacturer_id = md.manufacturer_id
          AND p.product_id = pts.product_id
          ORDER BY pd.name        
           "; 
           $query = $this-&gt;db-&gt;query($products_sql);
           return $query-&gt;rows;   
      }
  }
  </code></pre>
  
  <p>For the most part, this works fine.  Where it fails is the store_id portion.  The CASE will only return the first matched value, then go onto the next bit.  I need it to match and list ALL the stores that a product may show up in, same as it does for category.</p>
  
  <p>When the above didn't work I tried to set it up like the AS 'category' bit like so:</p>
  
  <pre><code>s.name as                   `Store`,
  
  CASE
  WHEN p.`is_dropshipped` = 1
  THEN 'Yes'
  ELSE 'No'
  END  AS                         `Is Dropshipped`        
  
  FROM
  product p,
  product_description pd,
  product_to_category ptc,
  category_description cd,
  manufacturer md,
  store s,
  product_to_store pts
  WHERE 1
  AND p.product_id = pd.product_id
  AND p.product_id = ptc.product_id
  AND ptc.category_id = cd.category_id
  AND p.manufacturer_id = md.manufacturer_id
  AND p.product_id = pts.product_id
  AND pts.store_id = s.store_id
  ORDER BY pd.name    
  </code></pre>
  
  <p>But that didn't work as expected because in the store table the default store is 0 and that table starts with the other stores of a multi store set up.  I.E. 1 and so on, but doesn't list the default, or 0 store.</p>
  
  <p>What would be the best way to fashion this query to get the results I'm hoping for?</p>