Possible Sub-query

[pleashelp]

I am Using SQL2005..

My query so far is this:

Select
Customer.Name
, Service.service
, Customer.IsCurrentPatient

from customer
Inner Join service on
Customer.person_id= service.person_id

Where Customer.IsCurrentPatient=’Y’

This Returns:

Sally Jones Meds
Sally Jones Office Visit
Tom Brown Meds
John Doe Office Visit
John Doe Therapy

What I want to see in my final results is any patient who has ONLY Meds (or) ONLY Meds AND Office Visit.

So in this example, I want to return Sally Jones because she has Meds and Office Visit; Tom Brown because he has only Meds; but NOT John Doe because he has Office Visit but no Meds.

Can anyone help me finish this query?

Thanks for any help with this.

 

[IanWaterman]

Select distinct
Customer.Name
, Customer.IsCurrentPatient

from customer
Inner Join service on
Customer.person_id= service.person_id

Where Customer.IsCurrentPatient='Y'
and Service.service in ('Meds', 'Office Visit')

Ian

 

[bborissov]

In short you need all that have MEDS

Select
 Customer.Name
, Service.service
, Customer.IsCurrentPatient
from customer
    Inner Join (SELECT person_id
                       FROM Service
               WHERE Service = 'Meds') Srvc
           ON Customer.person_id= Srvc.person_id
    Inner Join service on
           Customer.person_id= service.person_id
Where Customer.IsCurrentPatient='Y'

Where Customer.IsCurrentPatient='Y'


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[pleashelp]

I appreciate your quick response. Maybe I didn't give quite enough examples. But I tried that, and because it is possible that the patient could have meds, office visit AND Therapy, this query returns too many results. I only want to see the patients who only have meds OR meds and Office visit. I don't want to see them if they have any other type of service in addition to these two.

 

[simian336]

Try this....

with custlist as
--(
--Select Customer.Name, Service.service, Customer.IsCurrentPatient
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and (service= 'Meds' or service= 'Office Visit')
--)
--select name, count(name)
--from custlist
--group by name
--having count(name)=2
--
--union
--
--with custlist2 as
--(
--Select Name
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and service= 'Meds'
--union
--Select Name
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and (service= 'Meds' and service= 'Office Visit')
--
--)
--select distinct(name)
--from custlist2



Simi

 

[simian336]

Sorry about commenting it out but Tek-Tips ferquently messes up when I post code.

Just uncomment it.

Simi

 

[simian336]

My apologies again... posted to much code....
This is all you need.

--with custlist2 as
--(
--Select Name
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and service= 'Meds'

--union

--Select Name
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and (service= 'Meds' and service= 'Office Visit')
--
--)
--select distinct(name)
--from custlist2

Simi

 

[djj55]

Simi,
Look at the code section of

http://www.tipmaster.com/includes/tgmlinfo.cfm

Notice that you do not need html within the

 area.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[simian336]

I forgot that UNION does not allow duplicates so you do not need CTE.

--Select Name
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and service= 'Meds'
--union
--Select Name
--from customer
--Inner Join service
-- on Customer.person_id= service.person_id
--Where Customer.IsCurrentPatient='Y'
--and (service= 'Meds' and service= 'Office Visit')


PS, DJJ55 I have tried every combiation and it never works. If you know a specific code let me know cause it drives me insane.

Simi

 

[markros]

Select Customer.Name, Service.service, Customer.IsCurrentPatientfrom customer    Inner Join service on    Customer.person_id= service.person_idWhere Customer.IsCurrentPatient='Y' and exists (select 1 from
Service S where S.Person_ID = Service.Person_ID and 
S.Service IN ('Meds','Office Visit'))

There is a cool name for such problems, but unfortunately I forgot.

PluralSight Learning Library

 

[markros]

There should be a space before where in the above code. I use IE8 and unfortunately, when I copy code block, often CR is removed and I may forget to insert it manually.

PluralSight Learning Library

 

[markros]

Actually, my code above doesn't solve your exact problem because it's possible with this code to get people having ONLY Office Visits and no Meds.

The trick is to use grouping and having, give me a sec.

;with Persons_Visits as (select PersonID, count(Service) as Types_Of_Visit, sum(case when Service = 'Medical' then 1 end) as Medical_Visits, sum(case when Serive = 'Office' then 1 end) as Office_Visits from Service group by PersonID),
Our_Patients as (select PersonID from Persons_Visit 
where Types_Of_Visit = Medical_Visits + Office_Visits and Medical_Visits > 0)

select Customer.Name, Service.service, Customer.IsCurrentPatient
from customer    
Inner Join service on  
Customer.person_id= service.person_id
inner join Our_Patients on Customer.Person_ID = Our_Patients.Person_ID
Where Customer.IsCurrentPatient='Y'

PluralSight Learning Library

 

[markros]

The above represents an idea - I hope you'll fix couple of typos I introduced.

PluralSight Learning Library