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

Problem solved by Dave Novak at http://www.sqlservercentral.com/Foru....aspx?Update=1
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

This is a likely cartesian join when you get more data with this type of multiple joins......