title: mysql right join multiple tables
body: <p>I’m new to mySQL and I’m struggling to write a query that will list all stores where a price for a product has been scanned as well as the stores where it has not been scanned. The following gives the correct result for a single product:</p>
  
  <pre><code>select distinct(s.id) as store_id, s.chainID as chain_id, p1.productID as product_id, 
  s.chain, s.location, s.city, prd.brand, prd.product, prd.quantity, prd.size, prd.unit
  from analytics.price p1        -- Fact table with prices
  join analytics.pricetime pt   -- Dimension table with time a price was scanned
  on p1.priceTimeID = pt.id
  join analytics.product prd    -- Dimension table with products
  on p1.productID = prd.id
  and prd.published = 1
  right join analytics.store s   -- Dimension table with stores and the chain they belong to
  on p1.storeID = s.id
  and p1.chainID = s.chainID
  and p1.productID = 46720
  and p1.priceTimeID between 2252 and 2265
  where s.published=1
  and s.chainID = 5;
  </code></pre>
  
  <p>When I remove the <strong>p1.productID = 46720</strong> clause to get results for all products, I get all the stores that have scanned prices (correct), but the no price side of the right join only shows stores that have not had any prices scanned for any products. (This is a simple star schema with a price fact and dimensions of product, time and store). I would greatly appreciate help – I’ve tried this every way I can think of including “in”, “not exists” and stored procedure with cursor but I seem to hit a brick wall each way I try it.</p>
  
  <p>Edited to clarify:</p>
  
  <p>Here's what I'm trying to achieve:</p>
  
  <pre><code>Price table
  Product Chain       Store       Price
  100     5       1       $10
  101     5       2       $20
  
  Store table
  Chain   Store
  5       1
  5       2
  5       3
  
  Desired Result
  Product Chain   Store       Price
  100     5       1       $10
  100     5       2       NULL
  100     5       3       NULL
  101     5       1       NULL
  101     5       2       $20
  101     5       3       NULL
  
  
  Actual Result
  Product Chain   Store       Price
  100     5       1       $10
  101     5       2       $20
  NULL    5       3       NULL
  </code></pre>