Nested SQL statement

Posted on 2008-11-12
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 = AND md_cr_pending.status=2

here is my SQL statement that i came up with

SELECT, m.check_no, m.check_amount
(select sum(initial_amount) from md_cr_pending where status=2 and 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
    LVL 44

    Accepted Solution


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

    Open in new window


    Author Comment

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

    I have solved it though with this - from another expert

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

    LVL 44

    Expert Comment

    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.

    Author Comment

    yes, that was my bad....but regardless.....your solution still didn't work...
    LVL 44

    Expert Comment


    "...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

    >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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    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…
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now