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
