how to achive this select statement ?

Question

0.00/5 (No votes)

See more:

I have two table

PR_DET(IT_Code,ReqQty,ReqDate, IT_Desc)

PO_DET(IT_Code,IT_Status, PO_Qty)

I want to display

IT_Code, ReqQty, ReqDate, IT_Desc from PR_DET

and want add one more column Qty_Underpurchase
showing sum(PO_Qty) where PO_DET.IT_Status = 'UnderPurchase'

how can i achive this please help me out?

Posted 20-Nov-13 0:41am

Manzoor Ahmed P

Add a Solution

Comments

agent_kruger 20-Nov-13 6:47am

is there something that joins both the tables like a link?

Manzoor Ahmed P 20-Nov-13 6:48am

IT_Code is the link between two

1 solution

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)

Nicholas Marty · Answer 1 · 2013-11-20T00:57:00

Try something like this:

SQL

SELECT pr.IT_Code, pr.ReqQty, pr.ReqDate, pr.IT_Desc, SUM(po.PO_Qty) AS Qty_Underpurchase FROM PR_DET AS pr
INNER JOIN PO_DET AS po ON po.IT_Code = pr.IT_Code
WHERE po.IT_Status = 'UnderPurchase'
GROUP BY pr.IT_Code, pr.ReqQty, pr.ReqDate, pr.IT_Desc

For Joins: http://technet.microsoft.com/en-us/library/aa213233(v=sql.80).aspx[^]

If you use an aggregate function (like SUM is one) all other columns you want to show need to be either an aggregate function too or be in the GROUP BY clause.

Edit: Fixed the SQL (was missing a ")" ;) )