Query within a query (if that makes sense)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query within a query (if that makes sense)

Author

Topic

dutchgold647
Starting Member

13 Posts

Posted - 2012-09-06 : 01:57:35

Hi all,

I'm really new to this so please bear with me

Trying to figure out something with little or no success at the moment and you might be able to help me.

Each value in column PersonID is related to each person in the database. Only one is assigned per person.

What I want to find out is:
• All the PersonID’s that are in the stage "Accepted"
• Of these PersonID’s, only return ones where the ID does not exist in a previous stage of "Declined"

IE: ID no 123456 has made 1 application and is currently in Accepted: On Report
ID no 654321 has made 10 applications and is currently in Accepted. One of these previous 10 applications is in Declined: Not on Report

I've tried using something like the below to little effect:

SELECT [PersonID]

FROM [LoanFull]

(Select PersonID as PID from [LoanFull]
Where Stage = 'Declined') as PID

Where Sta_Desc like 'Accepted'
and PID.PersonID=LF.PersonID

Any suggestion would be much appreciated!

Cheers,

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-06 : 03:03:41

This would be easier with your schema, but I'll give it a shot:

SELECT DISTINCT PersonID
FROM LoanFull
WHERE Sta_Desc = 'Accepted'
AND PersonID NOT IN (SELECT DISTINCT PersonID FROM LoanFull WHERE Stage = 'Declined')

-Chad