Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 31st, 2008, 01:12 PM
Registered User
 
Join Date: Mar 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem with Join/Where

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


 
Old March 31st, 2008, 03:23 PM
Registered User
 
Join Date: Mar 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 12th, 2008, 01:25 AM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
inner join problem john-boy SQL Language 1 March 29th, 2006 06:30 AM
Inner Join Problem rylemer SQL Language 2 October 21st, 2005 04:41 PM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
JOIN problem kaz SQL Language 4 December 22nd, 2003 04:13 PM
SQL Inner Join Problem tp194 Classic ASP Databases 2 July 8th, 2003 09:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.