Philippe Rousselot wrote:
Hi
I have three linked tables : store, catalogue, and sales
store : ID_store, store, date catalogue : ID_product, product sales : ID_sales, ID_store, ID_product, product, quantity
I would like a view giving me ALL the products in catalogue with the quantity per store if the store has this product, and zero or null (or anything) if the store does not have it
ex.:
store ID_store 1 2
store new york paris
date 2003-10-10 2003-10-11
catalogue ID_product 1 2 3
product table chair lamp
sales
ID_sales 1 2 3
ID_store 1 1 2 ID_product 1 2 3
product table chair lamp
quantity 3 2 4
look for product and quantity in store new york view product table chair lamp quantity 3 2 0
look for product and quantity in store Paris view product table chair lamp quantity 0 0 4
thanks in advance
Philippe
You need a LEFT JOIN, and your store name/id filter needs to be in the left join's ON clause, rather than in the where clause. Here are a few examples, starting with a listing by store:
SELECT store.store, cat.product, sales.quantity FROM store, catalogue AS cat LEFT JOIN sales ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store) ORDER BY store.store, cat.ID_product;
+----------+---------+----------+ | store | product | quantity | +----------+---------+----------+ | New York | table | 3 | | New York | chair | 2 | | New York | lamp | NULL | | Paris | table | NULL | | Paris | chair | NULL | | Paris | lamp | 4 | +----------+---------+----------+
Change the NULL quantities to 0, using IFNULL:
SELECT store.store, cat.product, IFNULL(sales.quantity,0) quantity FROM store, catalogue AS cat LEFT JOIN sales ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store) ORDER BY store.store, cat.ID_product;
+----------+---------+----------+ | store | product | quantity | +----------+---------+----------+ | New York | table | 3 | | New York | chair | 2 | | New York | lamp | 0 | | Paris | table | 0 | | Paris | chair | 0 | | Paris | lamp | 4 | +----------+---------+----------+
Only look at store 1:
SELECT store.store, cat.product, IFNULL(sales.quantity,0) quantity FROM store, catalogue AS cat LEFT JOIN sales ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store) WHERE store.ID_store=1;
+----------+---------+----------+ | store | product | quantity | +----------+---------+----------+ | New York | table | 3 | | New York | chair | 2 | | New York | lamp | 0 | +----------+---------+----------+
Only look at store in Paris:
SELECT cat.product, IFNULL(sales.quantity,0) quantity FROM store, catalogue AS cat LEFT JOIN sales ON (cat.ID_product=sales.ID_product AND sales.ID_store=store.ID_store) WHERE store.store='Paris';
+---------+----------+ | product | quantity | +---------+----------+ | table | 0 | | chair | 0 | | lamp | 4 | +---------+----------+
Hope that helps.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
