Nested Select statement (error output)
Nested Select statement (error output)
(OP)
Hi, im newbie of SQL user
Note: I use in MySQL
I attach sql script called testdb.sql
ht
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
ht
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
Talk To Other Members
RE: Nested Select statement (error output)
CODE
, (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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Nested Select statement (error output)
Thanks you so much for the help.
Its works :D
thanks
regards
s1