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
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
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
ASKER
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
tigin44:, your statement works, but you are not using the criteria i asked for
cd_personal_checks.Status<
md_cr_pending.status=2
i will see if i can alter it though
thanks
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
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
FROM cd_personal_checks m
WHERE m.status <=2) A
where A.total <> 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yup, that will work - thanks!
from md_cr_pending
join cd_personal_checks m on b.cd_personal_checks_id=m.
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.
where status=2
and sum(b.initial_amount) >0