geocoins-software
asked on
Nested SQL statement
I am trying to extract info from two tables
cd_personal_checks
md_cr_pending
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
thanks
cd_personal_checks
md_cr_pending
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_
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
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
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For future reference, this question is a duplicate of
https://www.experts-exchange.com/questions/23900205/Nested-SQL-statement.html
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.
https://www.experts-exchange.com/questions/23900205/Nested-SQL-statement.html
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.
ASKER
yes, that was my bad....but regardless.....your solution still didn't work...
@geocoins-software
"...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.
"...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.
ASKER
>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
thanks
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
thanks
ASKER
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
thanks