List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 2 2006 1:35pm
Subject:Re: items quantity
View as plain text  
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
Thread
items quantityGyurasits Zolt├ín2 Feb
  • Re: items quantitynigel wood2 Feb
  • Re: items quantityPeter Brawley2 Feb