Thank you for your attention, Adolfo. Sorry, it doesn't work. It looks like quantities are added more times into the SUM function.
gigi > -----Messaggio originale----- > Da: Adolfo Bello [mailto:[EMAIL PROTECTED]] > Inviato: luned́ 16 dicembre 2002 19.04 > A: Gigi Di Leo; [EMAIL PROTECTED] > Oggetto: RE: Inventory Query > > > Try this: > > SELECT t1.id AS prod_id, t1.description AS > Description,SUM(t2.quantities) AS Purchases, > SUM(t3.quantities) AS Sellings, (Purchases-Sellings) AS Inventory > FROM Products t1 INNER JOIN Purchases t2 ON t1.id=t2.prod_id > INNER JOIN Sellings t3 ON t1.id=t3.prod_id > GROUP BY t1.id,t1.description > > > > > -----Original Message----- > > From: Gigi Di Leo [mailto:[EMAIL PROTECTED]] > > Sent: Monday, December 16, 2002 12:03 PM > > To: '[EMAIL PROTECTED]' > > Subject: Inventory Query > > > > > > Hello list. > > > > Could you please suggest me a one-line query to solve a > > problem of inventory ? > > > > Three table: > > > > 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 | > > ----------------------------- > > > > 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 | > > > --------------------------------------------------------------------- > > > > Thank you very much for your help. > > > > Gigi > > > > > > > --------------------------------------------------------------------- > > 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 --------------------------------------------------------------------- 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
