Nested Select statement (error output) 1

[soushi01]

Hi, im newbie of SQL user

Note: I use in MySQL
I attach sql script called testdb.sql

http://www.filefactory.com/file/cbe7a69/n/testdb.sql

I have 2 table which is product1,product2

Product1
PID ProductName ProductCode
1 Nike N1
2 Adidas A2
3 NewBalance NB1
4 Puma P3
.... etc

Product2
Date PID
2009/1/14 1
2009/1/15 1
2009/2/1 2
2009/3/3 3
.........etc

Expected out is

ProductName Jan2009 Feb2009 Mar2009
Adidas 1 1 0
NewBalance 30 0 1
Nike 2 0 0
Puma 20 0 0

But, i tried wrote Nested Select statement query like this, the output is wrong :
select product1.ProductName ,
(select count(product2.PID)
from product1 as product1 left join product2 as product2
on product1.pid=product2.pid
and product2.pdate BETWEEN '2009/1/1' AND '2009/1/31')
as 'Jan 2009',
(select count(product2.PID)as 'Feb 2009'
from product1 as product1 left join product2 as product2
on product1.pid=product2.pid
and product2.pdate BETWEEN '2009/2/1' AND '2009/2/28')as 'Feb 2009',
(select count(product2.PID)
from product1 as product1 left join product2 as product2
on product1.pid=product2.pid
and product2.pdate BETWEEN '2009/3/1' AND '2009/3/31') as 'Mar 2009'
from product1 Group by product1.productName;



My output looks like this
ProductName Jan2009 Feb2009 Mar2009
Nike 53 1 1
Adidas 53 1 1
NewBalance 53 1 1
Puma 53 1 1


any solution ?

thanks
Regards
s1

 

[PHV]

what about this ?

SELECT O.ProductName
, (SELECT COUNT(P2.PID) FROM product1 P1 LEFT JOIN product2 P2
    ON P1.PID=P2.PID AND P2.pdate BETWEEN '2009/1/1' AND '2009/1/31'
    WHERE P1.PID=O.PID) AS 'Jan 2009'
, (SELECT COUNT(P2.PID) FROM product1 P1 LEFT JOIN product2 P2
    ON P1.PID=P2.PID AND P2.pdate BETWEEN '2009/2/1' AND '2009/2/28'
    WHERE P1.PID=O.PID) AS 'Feb 2009'
, (SELECT COUNT(P2.PID) FROM product1 P1 LEFT JOIN product2 P2
    ON P1.PID=P2.PID AND P2.pdate BETWEEN '2009/3/1' AND '2009/3/31'
    WHERE P1.PID=O.PID) AS 'Mar 2009'
FROM product1 O

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[soushi01]

Hi, PH

Thanks you so much for the help.
Its works


thanks
regards
s1