Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
1... product (p_id(pk), subcate_id, p_name, p_qty)
2... cate(cate_id(pk), cate_name)
3... subcate(subcate_id(pk), cate_id, subcate_name )

 
where pk=PRIMARY KAY
 
here is also the concept of foreign key is used btween product tbl & subcate tbl as well as cate tbl & subcate tbl............ now i want that write a proc that takes the cate_id as argu & returns a tbl that contains some columns, something like that--->
 
tbl=(subcate_name, total_qty)

where subcate_name contains all those subcate names, which r corresponding to that cate_id, and the every total_qty cell contains the total number of products in that subcate
 
i hv tried some sql queries, but didnt work(little lack of knowledge in sql)
 
so just do me a favor, and write a suitable proc as required... please
 
Thank u in advance
Posted 13-Oct-12 10:30am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this:
select s.subcat_name, sum(p-p_qty) as total_qty from product p inner join subcate s where p.subcate_id=s.subcate_id group by s.subcat_name
  Permalink  
v3
Comments
damodara naidu betha at 15-Oct-12 1:51am
   
My 5..
Zoltán Zörgő at 15-Oct-12 1:54am
   
Thank you
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I'm not entirely sure I completely understand what it is you are trying to do here, but I think you are losing results where you don't have data?
 
This may be of use to you?
CREATE PROC schemaname.procsname(
  @SubCateID INT
) AS
BEGIN
  SELECT  s.subcate_name,
          SUM(ISNULL(p.p_Qty,0))TotalQty
  FROM    
  LEFT JOIN Product p
    ON    s.SubcateID = p.SubcateID
  GROUP BY s.subcate_Name
END
The left join will allow the subcate to show when there is no match at Product.
 
Let me know if I've got the wrong end of the stick with this.
  Permalink  
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 9,108
1 OriginalGriff 8,186
2 CPallini 2,613
3 Richard MacCutchan 2,231
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 13 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100