Need to meet multiple values in 1 column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need to meet multiple values in 1 column

Author

Topic

Treusser
Starting Member

3 Posts

Posted - 2011-08-04 : 15:10:18

I am new to SQL so this is probably a very basic query that I am trying to run but it has me stumped. I am not even sure I titled the topic correctly for what I am wanting to accomplish.

I have multiple tables that I need pulled together to find out which patients have had a certain combination of appointments, namely consults and surgeries.

I can pull the information for patients with 1 type of appointment, or all patients with either type, but not exclusively patients that have both. Obviously if I run the WHERE type=1 AND type=2 the results are blank

Here is my query so far for pulling patients with either type.

select patientst.userdefinedid, persont.last, persont.first,appointmentst.notes, appointmentst.starttime, appointmentpurposet.purposeid from patientst

inner join persont
on persont.id=patientst.personid

inner join appointmentst
on patientst.personid=appointmentst.patientid

inner join appointmentpurposet
on appointmentst.id = appointmentpurposet.appointmentid

where appointmentpurposet.purposeid in (394, 355)
order by last asc

What do I need to do so I can see only patients who have had both types of appointments?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-08-04 : 23:34:34

Try this script,

select persont.id,COUNT(*) AS CountChk INTO #TEMPPatient from patientst
inner join persont on persont.id=patientst.personid
inner join appointmentst on patientst.personid=appointmentst.patientid
inner join appointmentpurposet on appointmentst.id = appointmentpurposet.appointmentid
where appointmentpurposet.purposeid in (394, 355)
GROUP BY persont.id
HAVING COUNT(*) > 1

select patientst.userdefinedid, persont.last, persont.first,appointmentst.notes, appointmentst.starttime, appointmentpurposet.purposeid from patientst
inner join persont on persont.id=patientst.personid
inner join appointmentst on patientst.personid=appointmentst.patientid
inner join appointmentpurposet on appointmentst.id = appointmentpurposet.appointmentid
INNER JOIN #TEMPPatient ON #TEMPPatient.id=persont.id
order by last asc

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Treusser
Starting Member

3 Posts

Posted - 2011-08-05 : 12:37:59

Thanks for your response and script. Every little bit I see and use helps me understand it better.

I ran the script and it's still grabbing patients who've had either type of appointment, not just ones that have had both.

As a bit of background, we need to see which patients who've had a laser procedure(type 394), had a consult(type 355) beforehand.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-05 : 13:32:06

Maybe you can do something like this in a HAVING clause to get the results you want:

DECLARE @T TABLE (ID INT, Val INT)
INSERT @T (ID, Val)
VALUES
(1, 394),
(2, 394),
(3, 394),
(4, 394),
(5, 394),
(1, 899),
(2, 899),
(3, 899),
(4, 899),
(1, 355),
(3, 355),
(5, 355)


SELECT ID
FROM @T
GROUP BY ID
HAVING 
    SUM(CASE WHEN Val = 394 THEN 1 ELSE 0 END) > 0 
    AND SUM(CASE WHEN Val = 355 THEN 1 ELSE 0 END) > 0

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-10 : 15:03:58

quote:
What do I need to do so I can see only patients who have had both types of appointments?

You don't need to display purposeId because you know you want appointments with both purposes.

select pat.userdefinedid, prs.last, prs.first, app.notes, app.starttime
from patientst pat
join persont prs on prs.id=pat.personid
join appointmentst app on pat.personid=app.patientid
join appointmentpurposet srg on app.id = srg.appointmentid and srg.purposeId = 394
join appointmentpurposet cns on app.id = cns.appointmentid and srg.purposeId = 355
order by last asc

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources