List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:December 17 2003 7:25am
Subject:Re: query over several tables
View as plain text  
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

Thread
query over several tablesPhilippe Rousselot17 Dec
  • Re: query over several tablesMichael Stassen17 Dec