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