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
 SubSelect for additional conditions or better way?

Author  Topic 

ALSZ37
Starting Member

25 Posts

Posted - 2015-02-23 : 15:38:49
Hi All,

So I have a view that has the current status of a patient, but I want to pull ONLY patients that have had a status 10 and have had no status 43. Would the best method to have a subselect? I have something like this written, but i'm pulling back alot more data because a patient can have multiple status in the sub select. Any ideas?

select
a.patientid
a.patientname,
a.CurrentStatus,
a.StatusDate
from view a left join
(select pa_id, status from hospitalprogram) b on a.PatientID = b.pa_id
where (b.status = 10 and b.status <> 43)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-23 : 16:31:05
[code]
select
a.patientid
,a.patientname
,b.status
from view a
join hospitalprogram b on a.patientid = b.patientid
where not exists (select * from hospitalprogram c where b.patientid = c.patientid and c.status = 43)
and b.status = 10
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ALSZ37
Starting Member

25 Posts

Posted - 2015-02-23 : 19:11:47
quote:
Originally posted by tkizer


select
a.patientid
,a.patientname
,b.status
from view a
join hospitalprogram b on a.patientid = b.patientid
where not exists (select * from hospitalprogram c where b.patientid = c.patientid and c.status = 43)
and b.status = 10


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thank you tkizer!
Go to Top of Page
   

- Advertisement -