SubSelect for additional conditions or better way? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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/

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources