Avatar of Goo123
Goo123
Flag for United States of America asked on

Nested query 2008 , exclude second (nested query) possible?

Is it possible to have a nested query  where the nested query 'excludes' the record that is needed in the main query?  

i.e.  

All patient's that are in IP with financial classes NX, FL, SC and exclude patient's that are in TX and MP and in financial classes in NX, BL, TN,OK.  

This is what I thought would work, but doesn't??

Select patient type, financial class
From   demographics
Where patient type = IP
            and financial class = NX,FL,SC
            and patient type not in (select patient type
 from demographics
where patient type   in('tx','mp') and financial class in ('nx','bl','tn','ok'))
Microsoft Excel

Avatar of undefined
Last Comment
CEVM6

8/22/2022 - Mon
IrogSinta

I'm confused.  If your query is looking for all patients with type IP and are FinancialClass In ("NX","FL","SC"), you are already by default already excluding other patient types whether they are TX, MP or whatever else you may have.
ASKER CERTIFIED SOLUTION
KristianB

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Goo123

ASKER
See ya'lls point...The second part, I'm trying to exclude those where I write:

and pat type not in

This is how it should have read:

Select patient type, financial class

From   demographics

Where patient type = MH
and financial class = NX,FL,SC

and patient type not in (select * from demographics
                                            where patient type = IP
                                                        and financial class=TX,NL,NX)

Hope that makes more sense, but I understand about the first part, excluding IP already,  but is this the way you would "not include"  a particular field?  Wouldn't this be how you would 'pass' on a field to the main query?
IrogSinta

Sorry, it still doesn't make sense for me.  Perhaps if you gave us some sample data and the results you want to see.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Goo123

ASKER
OK can do tomorrowthanks
Goo123

ASKER
Oops, not abandoned...just slept..

The problem is this:

To pull in patient currently in "AR" status that has an "ER" or "ERP" patient type and also financial class of "MH.

But, also exclude any patient's above, that also has had an 'IP" patient that has been in "PA" or "AR" status and also financial class of 'MZ','MP',MA,'CA', OR 'MB'.

Nested query for second part?
IrogSinta

Can you show us some sample data from your table (without the patient's names of course)?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Goo123

ASKER
That is IP patient type.. its in STAR SQL really a screwy way and database.   I'm use SQL
Goo123

ASKER
Wil this help?

Patient type =ER,EP, and  exclude IP

Acct_loc =AR,    exclude patient type's with IP that have AR and PA

Fin_class = MH, but exclude patient type's with IP and AR and Pa acct_loc and those with 'MZ','MP',MA,'CA', OR 'MB' fin_class
IrogSinta

Rather than saying what should and shouldn't be included, it would really help if you could show some sample data of what you have right now and what you would like to remain.  What you've given so far may make sense to you because you know the make-up of your table (the column names and the data you currently have in each particular column).  Sorry, but without more info, we can only keep guessing what it is you want.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Goo123

ASKER
X
OK
Goo123

ASKER
OK
CEVM6

You could try working the logic a different way such as:

Acct_loc =AR
and
Fin_class = MH
and
Patient type in ER,EP

Hope this helps.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.