Hi Gigi:
I got a 2 steps solution to your problem:
1.- Create a TEMPORARY TABLE with
CREATE TEMPORARY TABLE anyname SELECT
t1.id,t1.description,SUM(t2.quantities) AS Purchases,t3.quantities FROM
products t1 INNER JOIN purchases t2 ON t1.id=t2.id INNER JOIN sellings
t3 ON t1.id=t3.id GROUP BY t1.id.t1.description,t3.quantities;
2.- SELECT FROM anyname id,description,Purchases, SUM(quantities) AS
Sellings, (Purchases-SUM(quantities)) AS Inventory GROUP BY
id,description,Purchases;
Adolfo
> -Original Message-
> From: Gigi Di Leo [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, December 17, 2002 11:53 AM
> To: [EMAIL PROTECTED]
> Subject: JOIN based query problem (little help needed)
>
>
> Hello list.
>
> Please help me to refine a JOIN based query which I am not
> able to correct. This the scenario (simplified).
>
> Three tables:
>
> Products
> -
> | id | description |
> -
> | 01 | bread|
> | 02 | milk |
> | 03 | coffee |
> -
>
> Purchases
> -
> | prod_id | quantities |
> -
> | 01| 10 |
> | 02| 5 |
> | 03| 3 |
> -
>
> Sellings
> -
> | prod_id | quantities |
> -
> | 01| 3 |
> | 01| 1 |
> | 02| 1 |
> | 02| 1 |
> -
>
> This the query which I could figure out:
>
> SELECT products.*,
>SUM(purchases.quantity) AS purchases,
>SUM(sellings.quantity) AS sellings,
>SUM(purchases.quantity)-SUM(sellings.quantity) AS
> inventory FROM products LEFT JOIN purchases ON
> products.id=purchases.prod_id LEFT JOIN sellings ON
> products.id=sellings.prod_id GROUP BY products.id ORDER BY products.id
>
> The query should return:
> -
> | prod_id | description | purchases | sellings | inventory |
> -
> |01 | bread|10 | 4 | 6 |
> |02 | milk |5 | 2 | 3 |
> |03 | coffee |3 | 0 | 3 |
> -
>
> This is what the query actually returns:
> -
> | prod_id | description | purchases | sellings | inventory |
> -
> |01 | bread|20 | 4 |16 |
> |02 | milk |10 | 2 | 8 |
> |03 | coffee |3 | 0 | 3 |
> -
>
> Thank you very much for your help.
>
> Gigi
>
>
> Here is the database dump if you wish to reproduce the scenario:
>
> # phpMyAdmin MySQL-Dump
> # version 2.3.2
> # http://www.phpmyadmin.net/ (download page)
> #
> # Host: localhost
> # Generato il: 17 Dic, 2002 at 04:45 PM
> # Versione MySQL: 3.23.53
> # Versione PHP: 4.2.3
> # Database : `inventory`
> #
>
> #
> # Struttura della tabella `products`
> #
>
> CREATE TABLE products (
> id int(11) NOT NULL auto_increment,
> description varchar(64) NOT NULL default '',
> PRIMARY KEY (id),
> KEY id (id)
> ) TYPE=MyISAM;
>
> #
> # Dump dei dati per la tabella `products`
> #
>
> INSERT INTO products VALUES (1, 'bread');
> INSERT INTO products VALUES (2, 'milk');
> INSERT INTO products VALUES (3, 'coffee');
> #
>
> #
> # Struttura della tabella `purchases`
> #
>
> CREATE TABLE purchases (
> prod_id int(11) NOT NULL default '0',
> quantity int(11) NOT NULL default '0',
> KEY prod_id (prod_id)
> ) TYPE=MyISAM;
>
> #
> # Dump dei dati per la tabella `purchases`
> #
>
> INSERT INTO purchases VALUES (1, 10);
> INSERT INTO purchases VALUES (2, 5);
> INSERT INTO purchases VALUES (3, 3);
> #
>
> #
> # Struttura della tabella `sellings`
> #
>
> CREATE TABLE sellings (
> prod_id
