• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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
0
geocoins-software
Asked:
geocoins-software
1 Solution
 
i2mentalCommented:
SELECT m.id, m.check_no, m.check_amount, sum(b.initial_amount)
from md_cr_pending
join cd_personal_checks m on b.cd_personal_checks_id=m.id
where status=2

This will only give you rows that have a row in cd_personal_checks that matches the check ID in the other table.

If that still gives you 0, then the table has values, but they are all 0's. Try this instead

SELECT m.id, m.check_no, m.check_amount, sum(b.initial_amount)
from md_cr_pending
join cd_personal_checks m on b.cd_personal_checks_id=m.id
where status=2
and sum(b.initial_amount) >0
0
 
tigin44Commented:
try this

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 = m.status and m.status <=2
group by m.id, m.check_no, m.check_amount
0
 
geocoins-softwareAuthor Commented:
i2mental  - i get error when running your second statement - haven't tried your first yet - but i will now

tigin44:, your statement works, but you are not using the criteria i asked for

 cd_personal_checks.Status<=2
 md_cr_pending.status=2

i will see if i can alter it though

thanks
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
check this:
select * from (
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) A
where A.total <> 0
0
 
tigin44Commented:
i missed that part... so this will produce what u want

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
0
 
geocoins-softwareAuthor Commented:
Yup, that will work - thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now