March 31, 2008 at 1:32 pm
[font="Courier New"]I'm trying to create a view of data that requires several joins and some WHERE clause restrictions. Consider a simplified scenario with the following 2 simplified tables:
PATIENTS
pt_id name dob
----- ----- --------
1 John 19700301
2 Jane 19520512
CHARGES
ch_id pt_id amt sequence
----- ----- --- --------
10 1 100 1
20 1 50 2
30 2 75 1
40 1 25 3
I want to join the tables into a single result set that is structured as follows:
RESULTSET
name dob charge1 charge2 charge3
----- -------- ------- ------- -------
John 19700301 100 50 25
Jane 19520512 75 null null
I've tried the following code:
SELECT p.name, p.dob, c1.charge, c2.charge, c3.charge
FROM patients p
LEFT OUTER JOIN charges c1 ON p.pt_id = c1.pt_id
LEFT OUTER JOIN charges c2 ON p.pt_id = c2.pt_id
LEFT OUTER JOIN charges c3 ON p.pt_id = c3.pt_id
WHERE c1.sequence = 1
AND c2.sequence = 2
AND c3.sequence = 3
Unfortunately, no charges appear for Jane because she has no charges records with a sequence of 2 or 3. Can anyone help with this? I know there must be some way to conditionally join, but so far, I've found little information online. Thanks in advance for any help!
-Jason[/font]
March 31, 2008 at 1:54 pm
Your WHERE clause is prompting the optimizer to turn this into an INNER JOIN, and not an outer join.
Meaning - by not allowing for c1.sequence to be null, it CAN'T be an outer join. Same for c2.sequence and c3.sequence.
You should look at using the PIVOT syntax for this kind of query. It should yield something quite a bit more efficient than what you're looking at. Of course - denormalizing has its limits, no matter how you do it.
Even if you were to mostly keep your current syntax - you would do well to move those criteria into 3 separate derived tables.
SELECT p.name, p.dob, c1.charge, c2.charge, c3.charge
FROM patients p
LEFT OUTER JOIN
(select ptid, amt as charge from charges WHERE sequence = 1) c1 ON p.pt_id = c1.pt_id
LEFT OUTER JOIN
(select ptid, amt as charge from charges WHERE sequence = 2) c2 ON p.pt_id = c2.pt_id
LEFT OUTER JOIN
(select ptid, amt as charge from charges WHERE sequence = 3) c3 ON p.pt_id = c3.pt_id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 31, 2008 at 2:09 pm
a group by can do what you've described.
select P.pt_id, min(P.name) as name, min(P.dob) as dob,
min( case when C.sequence = 1 then C.amt else null end ) as charge1,
min( case when C.sequence = 2 then C.amt else null end ) as charge2,
min( case when C.sequence = 3 then C.amt else null end ) as charge3
from patients P join charges C on p.pt_id = c.pt_id
where C.sequence in (1,2,3)
group by P.pt_id
March 31, 2008 at 2:10 pm
Using the derived tables is one solution. Here is another that looks very similiar:
SELECT p.name, p.dob, c1.charge, c2.charge, c3.charge
FROM patients p
LEFT OUTER JOIN charges c1 ON p.pt_id = c1.pt_id and c1.sequence = 1
LEFT OUTER JOIN charges c2 ON p.pt_id = c2.pt_id and c2.sequence = 2
LEFT OUTER JOIN charges c3 ON p.pt_id = c3.pt_id and c3.sequence = 3
Dave Novak
March 31, 2008 at 2:11 pm
Another solution is this:
create table dbo.patient (
pt_id int,
pt_name varchar(20),
pt_dob datetime
)
go
create table dbo.charge (
ch_id int,
pt_id int,
ch_amt decimal(10,2),
ch_sequence int
)
go
insert into dbo.patient(pt_id, pt_name, pt_dob)
select 1,'John','19700301' union all
select 2,'Jane','19520512'
go
insert into dbo.charge(ch_id, pt_id, ch_amt, ch_sequence)
select 10, 1, 100.00, 1 union all
select 20, 1, 50.00, 2 union all
select 30, 2, 75.00, 1 union all
select 40, 1, 25.00, 3
go
select * from dbo.patient
select * from dbo.charge
go
with PatientCharge(
pc_id,
pc_name,
pc_dob,
pc_sequence,
pc_amt
) as (
select
pt.pt_id,
pt.pt_name,
pt.pt_dob,
ch.ch_sequence,
ch.ch_amt
from
dbo.patient pt
inner join dbo.charge ch
on (pt.pt_id = ch.pt_id)
)
select
pc_name,
pc_dob,
[1] as Charge1,
[2] as Charge2,
[3] as Charge3
from
(select
pc_id,
pc_name,
pc_dob,
pc_sequence,
pc_amt
from
PatientCharge) pc
pivot (max(pc_amt)
for pc_sequence in ([1],[2],[3])) as pvt
order by
pc_id
go
drop table dbo.patient
drop table dbo.charge
go
I actually got to try my hand at PIVOT.
😎
March 31, 2008 at 2:20 pm
DAVNovak (3/31/2008)
Using the derived tables is one solution. Here is another that looks very similiar:SELECT p.name, p.dob, c1.charge, c2.charge, c3.charge
FROM patients p
LEFT OUTER JOIN charges c1 ON p.pt_id = c1.pt_id and c1.sequence = 1
LEFT OUTER JOIN charges c2 ON p.pt_id = c2.pt_id and c2.sequence = 2
LEFT OUTER JOIN charges c3 ON p.pt_id = c3.pt_id and c3.sequence = 3
Dave Novak
This is a perfect simple solution (which is important as this is for a very large SQL query...30+ tables) that meets the output requirements...better than an Altoid-slap-to-the-cerebellum! Thanks!
April 2, 2008 at 7:15 am
I have a similar problem joining some other tables. Lets assume the aforementioned PATIENT table and the following:
[font="Courier New"]
APPOINTMENTS
appt_id pt_id appt_date appt_time
------- ----- --------- ---------
1 1 20080114 1330
2 1 20080114 1400
3 2 20080304 0930
[/font]
In this case I want a resultset containing only 1 instance of each patient joined to only 1 instance of an appointment (earliest appt_time), however when I LEFT OUTER JOIN PATIENTS and APPOINTMENTS, I get duplicative patients (when that patient has multiple appointments). Any thoughts?
April 2, 2008 at 7:31 am
Try using the new ROW_NUMBER() feature
;with PatientvisitCTE as (
select appointmentID,
pt_id,
appt_date,
appt_time,
Row_number() over (Partition by Pt_id, appt_date ORDER BY appt_time) RN
from Appointments)
Select appointmentID,
pt_id,
appt_date,
appt_time
from PatientvisitCTE
where RN=1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 7:38 am
This one is going to be a little more difficult. You are going to have to combine the date and time into a datatime field and then find the MIN value in a derived table. Here is a sample of what it would look like:
SELECT a.*, b.Appointment
FROM Patients a
LEFT JOIN (SELECT pt_id, MIN(CONVERT(datetime, appt_date + ' ' + LEFT(appt_time, 2) + ':' + RIGHT(appt_time, 2)) AS Appointment
FROM Appointments
GROUP BY pt_id) b
ON a.pt_id = b.pt_id
Dave Novak
April 2, 2008 at 9:52 am
Ok, after 335 lines of SQL and 778 lines of VB, this trial-by-fire SQL is complete and working! Thanks for your help Dave!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply