Click here to Skip to main content
12,897,945 members (86,424 online)
Rate this:
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
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
damodara naidu betha 15-Oct-12 1:51am
My 5..
Zoltán Zörgő 15-Oct-12 1:54am
Thank you
Rate this: bad
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
  SELECT  s.subcate_name,
  LEFT JOIN Product p
    ON    s.SubcateID = p.SubcateID
  GROUP BY s.subcate_Name

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.

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170424.1 | Last Updated 13 Oct 2012
Copyright © CodeProject, 1999-2017
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