Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nested Select statement (error output) 1

Status
Not open for further replies.

soushi01

Technical User
Dec 27, 2010
31
MY
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
 
what about this ?
Code:
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
 
Hi, PH

Thanks you so much for the help.
Its works :D


thanks
regards
s1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top