How can i achieve this type of select query ?

Question

0.00/5 (No votes)

See more:

I have two tables

MPL_DET(JobNo(pk), SystName, ReqQty )

MPL_ITDET( ID(pk), JobNo(FK), IT_Name, QtyPSys, IssQty)

I want to select the JobNo from MPL_DET where

MPL_ITDET.IssQty < MPL_ITDET.QtyPSys * MPL_DET.ReqQty

(Problem is MPL_DET.ReqQty will give multiple records
but i want the record belongs to perticular JobNo)

Sorry if my question is not clear!

Posted 12-Nov-13 22:13pm

Manzoor Ahmed P

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)

prashant patil 4987 · Accepted Answer · 2013-11-12T22:18:00

Solution 1

try this

SQL

SELECT JobNo from MPL_DET MD INNER JOIN MPL_ITDET MID ON MD.JobNo = MID.JobNo
WHERE MID.IssQty < ( MID.QtyPSys * MD.ReqQty )

Posted 12-Nov-13 22:18pm

prashant patil 4987

Comments

OriginalGriff 13-Nov-13 7:16am

If you are going to change someone else's answer, it is polite to mark what you have done.
In this case, you added the word "INNER" to my solution which is superfluous: a JOIN is an INNER JOIN unless otherwise specifically stated. You only need to use INNER when your query contains other forms of JOIN as well to reduce any possible confusion.
In this case, INNER is not needed as there is only the one JOIN in the query, and I have removed it from my answer again.

prashant patil 4987 14-Nov-13 0:53am

ok.. i got it OriginalGriff
Thanks for information.

OriginalGriff 13-Nov-13 7:18am

Particularly when your change makes the query then not work because you removed the table alias from it to get the INNER in!

prashant patil 4987 14-Nov-13 0:54am

I thouht u miss the word INNER thatsy m correcting your answer.
but now i got your point.
thanks
Prashant Patil
From INDIA.

OriginalGriff · Accepted Answer · 2013-11-12T22:18:00

Solution 2

Try:

SQL

SELECT d.JobNo FROM MPL_ITEDT i
JOIN MPL_DET d 
ON i.JobNo=d.JobNo
WHERE i.IssQty < (i.QtyPsys * d.ReqQty)

[edit]Rolled back superfluous changes made by other - OriginalGriff[/edit]

Posted 12-Nov-13 22:18pm

OriginalGriff

Updated 13-Nov-13 1:16am

v4

Comments

Manzoor Ahmed P 13-Nov-13 5:42am

Thank you friends!

OriginalGriff 13-Nov-13 5:49am

You're welcome!