[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Nested SQL statement

Posted on 2008-11-12
6
Medium Priority
?
209 Views
Last Modified: 2012-05-05
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
Comment
Question by:geocoins-software
6 Comments
 
LVL 8

Expert Comment

by:i2mental
ID: 22945207
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
 
LVL 26

Expert Comment

by:tigin44
ID: 22945365
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
 

Author Comment

by:geocoins-software
ID: 22945611
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 41

Expert Comment

by:Sharath
ID: 22946609
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
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 22948919
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
 

Author Closing Comment

by:geocoins-software
ID: 31516199
Yup, that will work - thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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