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
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006
