Gyurasits,
>I have a query:
>SELECT
> i1.item_ID,
> SUM(i1.quant) as Incoming,
> SUM(IF(i2.quant is NULL, 0,i2.quant)) as Outgoing
>FROM header h1
>INNER JOIN items i1 ON i1.header_ID=h1.id
>LEFT JOIN header h2 ON h2.type_="2"
>LEFT JOIN items i2 ON i2.header_ID=h2.id AND i1.item_ID=i2.item_ID
>WHERE h1.type_="1"
>GROUP BY i1.item_ID
I think you need just one join. Let aggregation do the work:
SELECT
i.item_ID,
SUM(IF( h.type_=1, IFNULL( i.quant, 0 ), 0 )) AS Incoming,
SUM(IF( h.type_=2, IFNULL( i.quant, 0 ), 0 )) AS Outgoing
FROM header AS h
INNER JOIN items AS i ON h.id=i.header_ID
GROUP BY i.item_ID;
PB
-----
Gyurasits Zoltán wrote:
Hi ALL!
Please help....
I have 2 tables...
header
--------------
id
type_
DATA:
---------------
id type_
1 1
2 1
3 2
items
--------------
header_id
item_id
quantity
DATA:
----------------
header_id item_id quant
1 1 10
1 2 20
2 1 100
2 2 200
3 1 20
3 2 15
"header" is the moving type, and "items" is the items table.
If header.type_ is "1" then incoming move, if "2" outgoing move.
I would like to calculate incoming and outgoing quantity of items. (stock)
I have a query:
SELECT
i1.item_ID,
SUM(i1.quant) as Incoming,
SUM(IF(i2.quant is NULL, 0,i2.quant)) as Outgoing
FROM header h1
INNER JOIN items i1 ON i1.header_ID=h1.id
LEFT JOIN header h2 ON h2.type_="2"
LEFT JOIN items i2 ON i2.header_ID=h2.id AND i1.item_ID=i2.item_ID
WHERE h1.type_="1"
GROUP BY i1.item_ID
Result:
item_ID Incoming Outgoing
1 110 40
2 220 30
The incoming is OK, but the outgoing is wrong .... I can't find solution!
I have mysql v4.0.18 I can't use subselect!!
Thanx!
Best Regards!
-----------------------------------------------------------------------------------------------------------
USE test;
#
# Table structure for table 'header'
#
DROP TABLE IF EXISTS header;
CREATE TABLE header (
id char(18) NOT NULL default '',
type_ tinyint(3) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table 'header'
#
INSERT INTO header VALUES("1", "1");
INSERT INTO header VALUES("2", "1");
INSERT INTO header VALUES("3", "2");
#
# Table structure for table 'items'
#
DROP TABLE IF EXISTS items;
CREATE TABLE items (
header_id char(18) NOT NULL default '',
item_id char(18) NOT NULL default '',
quant int(3) unsigned default NULL,
PRIMARY KEY (header_id,item_id),
KEY header_id (header_id,item_id)
) TYPE=MyISAM;
#
# Dumping data for table 'items'
#
INSERT INTO items VALUES("1", "1", "10");
INSERT INTO items VALUES("1", "2", "20");
INSERT INTO items VALUES("2", "1", "100");
INSERT INTO items VALUES("2", "2", "200");
INSERT INTO items VALUES("3", "1", "20");
INSERT INTO items VALUES("3", "2", "15");
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006
|
