if condition for sql query

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

if(a.WH_NO=b.WH_NO)

SELECT a.PRODUCT_NO,b.PRODUCT_NAME,a.QTY_IN,b.QTY_OUT,a.UNIT_COST FROM STOCKDETAILS a, STOCKDETAILS b WHERE a.PRODUCT_NO=b.PRODUCT_NO and a.WH_NO='WH1' AND a.STOCK_JR='IN' AND b.STOCK_JR='OU'

if(a.WH_NO!=b.WH_NO)

SELECT a.PRODUCT_NO,b.PRODUCT_NAME,a.QTY_IN,a.UNIT_COST FROM STOCKDETAILS a, STOCKDETAILS b WHERE a.PRODUCT_NO=b.PRODUCT_NO and a.WH_NO='WH1' AND a.STOCK_JR='IN' AND b.STOCK_JR='OU'

i want these queries execute at single result set

Posted 11-Oct-14 0:25am

navya chowdary

Add a Solution

Comments

navya chowdary 11-Oct-14 6:26am

plzzzzzzzzzzzzzz help me..

CHill60 11-Oct-14 6:28am

Comments like this do not encourage people to help. The word is "please" and you only posted your question a few moments ago

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)

CHill60 · Accepted Answer · 2014-10-11T00:54:00

Solution 1

Firstly to get these into a single result set you need both queries to return the same number of columns in the result.

CASE [^]is the construct you need, not IF

Try this (untested)

SQL

SELECT a.PRODUCT_NO,b.PRODUCT_NAME,a.QTY_IN,
QTY_OUT = CASE WHEN a.WH_NO=b.WH_NO THEN b.QTY_OUT ELSE 0 END
,a.UNIT_COST
FROM STOCKDETAILS a,
INNER JOIN STOCKDETAILS b ON a.PRODUCT_NO=b.PRODUCT_NO AND b.STOCK_JR='OU'
WHERE a.WH_NO='WH1' AND a.STOCK_JR='IN'

Posted 11-Oct-14 0:54am

CHill60

Updated 11-Oct-14 2:26am

v2

Comments

navya chowdary 11-Oct-14 7:02am

thank u...

[no name] 11-Oct-14 7:45am

If it helped: There is a button to accept and also you can vote a 5...same I voted ;)

CHill60 11-Oct-14 7:47am

Thank you!

navya chowdary 11-Oct-14 8:06am

can you write this another way if it is possible

CHill60 11-Oct-14 8:18am

What other way were you thinking of? Why?

navya chowdary 11-Oct-14 8:20am

the result giving double values,based on my products in WH_NO

CHill60 11-Oct-14 8:26am

Seems reasonable if StockDetails.QTY_OUT is held as a double! Your original queries would have done the same thing. If you're concerned about how they look on the screen then format them when you display them.
Looking again at the query as well it does seem a strange join back to the same table - I'm going to adjust my solution a little

navya chowdary 11-Oct-14 8:28am

OK

Maciej Los 11-Oct-14 8:39am

+5!

CHill60 11-Oct-14 8:41am

Thank you Maciej!

navya chowdary 13-Oct-14 0:21am

here i want if product no,product name are name in WH1 then i want QTY_IN sum value

CHill60 13-Oct-14 4:14am

Sorry I don't get what you mean. Is this another query?