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'))
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'))
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
Sorry, it still doesn't make sense for me. Perhaps if you gave us some sample data and the results you want to see.
ASKER
OK can do tomorrowthanks
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?
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?
Can you show us some sample data from your table (without the patient's names of course)?
ASKER
That is IP patient type.. its in STAR SQL really a screwy way and database. I'm use SQL
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
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
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.
ASKER
X
OK
OK
ASKER
OK
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.
Acct_loc =AR
and
Fin_class = MH
and
Patient type in ER,EP
Hope this helps.