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 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 `sellings`
> #
>
> INSERT INTO sellings VALUES (1, 3);
> INSERT INTO sellings VALUES (1, 1);
> INSERT INTO sellings VALUES (2, 1);
> INSERT INTO sellings VALUES (2, 1);
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
