Hi, im newbie of SQL user
Note: I use in MySQL
I attach sql script called 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
Note: I use in MySQL
I attach sql script called 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