[Last Call] Learn how to a build a cloud-first strategyRegister Now


Nested SQL statement

Posted on 2008-11-12
Medium Priority
Last Modified: 2013-11-23
I am trying to extract info from two tables


I would like to select id, check_no, & check_amount from cd_personal_checks where Status<=2

I would also like to select the total of initial_amount of all those records in md_cr_pending where the md_cr_pending.cd_personal_checks_id =  cd_personal_checks.id AND md_cr_pending.status=2

here is my SQL statement that i came up with

SELECT m.id, m.check_no, m.check_amount
(select sum(initial_amount) from md_cr_pending where status=2 and cd_personal_checks_id=m.id) as total
FROM cd_personal_checks m
WHERE m.status <=2

however, i also get records where the total = 0 (that is, there are no md_cr_pending records to total)

I would like only those records that have corresponding md_cr_Pending records to total

Question by:geocoins-software
  • 3
  • 3
LVL 46

Accepted Solution

aikimark earned 1500 total points
ID: 22947158

SELECT m.id, m.check_no, m.check_amount, sum(p.initial_amount) as total 
FROM cd_personal_checks m Inner Join md_cr_pending p 
On (p.id=m.id) 
WHERE m.status <=2
And p.status=2 
Group By m.id, m.check_no, m.check_amount 
Having sum(p.initial_amount) > 0

Open in new window


Author Comment

ID: 22951514
It worked, but, Sorry, that returned no records.

I have solved it though with this - from another expert

SELECT m.id, m.check_no, m.check_amount, sum(initial_amount) as total
FROM cd_personal_checks m inner join md_cr_pending p on m.id = p.cd_personal_checks_id and p.status = 2 and m.status <=2
group by m.id, m.check_no, m.check_amount

LVL 46

Expert Comment

ID: 23037908
For future reference, this question is a duplicate of

Rather than one question being posted in several Topic Areas, separate questions were posted in multiple TAs.  The "from another expert" refers to a solution posted in the other thread.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 23041607
yes, that was my bad....but regardless.....your solution still didn't work...
LVL 46

Expert Comment

ID: 23042691

"...your solution still didn't work"

I'm not asking for credit/points for solving your problem.  Both my suggested SQL and the accepted answer in the other thread are almost identical, using a Group By instead of a (Select ) column.  The results difference seems to be the table-linking criteria.

In the future, when you post your questions, please keep in mind that we don't have a copy of your database with which to validate and test our SQL prior to suggesting solutions.  There is some expectation that you will provide us enough information to solve your problem and that you are responsible for both testing the solutions and providing feedback to the experts.

Author Comment

ID: 23046288
>responsible for both testing the solutions and providing feedback to the experts

and I believe that is exactly what i did....unless of course you didn't see my comment

>It worked, but, Sorry, that returned no records

which of course assumes that i tested it - if of course i am stating it didn't return any records

Thanks for the lecture though.

I consider this closed...please do the same



Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question