mysql right join multiple tables

Question

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:

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;

When I remove the p1.productID = 46720 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.

Edited to clarify:

Here's what I'm trying to achieve:

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

Answer 1

I prefer the readability of using a LEFT JOIN -- this should return all published stores in chainid 5 and the associated products (given the criteria).

select distinct s.id as store_id, s.chainID as chain_id, s.chain, s.location, s.city, 
    prd.id as product_id, prd.brand, prd.product, prd.quantity, prd.size, prd.unit
from analytics.store s   
    left join analytics.price p1        
        on p1.storeID = s.id
            and p1.chainID = s.chainID
            and p1.priceTimeID between 2252 and 2265
    left join analytics.product prd    
        on p1.productID = prd.id
            and prd.published = 1
    left join analytics.pricetime pt   
        on p1.priceTimeID = pt.id
where s.published=1
    and s.chainID=5;

EDIT -- Give comments, it looks like you're looking for a Cartesian Product:

SELECT P.Product, P.Chain, S.Store, IF(P.Store=S.Store,P.Price,NULL) Price
FROM Price P, Store S
WHERE P.Chain = 5
  AND S.Chain = P.Chain
ORDER BY P.Product, S.Store

SQL Fiddle Demo