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
