can tbl contains extra columns(not exist, calculated at the time of execution) in sql proc

Question

0.00/5 (No votes)

See more:

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

ashokdamani

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Zoltán Zörgő** · Answer 1 · 2012-10-13T10:40:00

Solution 1

Try this:

SQL

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

Posted 13-Oct-12 10:40am

Zoltán Zörgő

Updated 13-Oct-12 10:47am

v3

Comments

damodara naidu betha 15-Oct-12 1:51am

My 5..

Zoltán Zörgő 15-Oct-12 1:54am

Thank you

Unsy · Answer 2 · 2012-10-13T10:43:00

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?

SQL

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.