Left Join Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Left Join Help

Author

Topic

macdca44
Starting Member

10 Posts

Posted - 2015-02-15 : 10:36:59

I have some sybase queries im trying to write in microsoft sql , and the left jins dont appear to work in the same way as they did. I have outer joine and have tried embedded selects to avoid having something in the where that makes the left join into an = join, but im still not getting what I want.

I want the script below to display one row only, where there is a primary flag on oncologyst and primary on doctor, and its giving me 2 rows, the one I want and one with a blank oncologust - can someone help?

Thanks

SELECT DISTINCT

Patient.PatientSer,
Patient.CreationDate,
Course.CourseId,
CHI=Patient.PatientId2,

Oncologist=(Select (Doctor.AliasName) FROM Doctor
WHERE Patient.PatientSer = Course.PatientSer
AND Patient.PatientSer = PatientDoctor.PatientSer
AND PatientDoctor.ResourceSer = Doctor.ResourceSer
AND PatientDoctor.OncologistFlag = 1 and PatientDoctor.PrimaryFlag = 1 )

--Oncologist = CASE WHEN PatientDoctor.OncologistFlag = 1 AND PatientDoctor.PrimaryFlag = 1 THEN Doctor.AliasName END

FROM Patient

JOIN Course
ON Patient.PatientSer = Course.PatientSer

JOIN PatientDoctor
ON Patient.PatientSer = PatientDoctor.PatientSer

JOIN Doctor
ON PatientDoctor.ResourceSer = Doctor.ResourceSer

WHERE

--PatientDoctor.OncologistFlag = 1 AND
--PatientDoctor.PrimaryFlag = 1 AND
( Upper(Substring(Patient.PatientId,1,1)) not in ('A' , 'B' , 'C' , 'D' , 'E' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') ) AND
( Upper(Substring(Course.CourseId,1,1)) not in ('A' , 'C' , 'D' , 'E' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') ) AND
Patient.PatientId = 'X'

ORDER BY Patient.PatientId ;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-15 : 14:21:29

Let's simplify! What do you get with this:


SELECT DISTINCT

Patient.PatientSer,
Patient.CreationDate,
Course.CourseId, 
CHI=Patient.PatientId2, 
Oncologist= Doctor.aliasname

FROM Patient

JOIN Course
ON Patient.PatientSer = Course.PatientSer

JOIN PatientDoctor
ON Patient.PatientSer = PatientDoctor.PatientSer 

JOIN Doctor
ON PatientDoctor.ResourceSer = Doctor.ResourceSer 

WHERE Patient.PatiendId = 'X' AND
      PatientDoctor.OncologistFlag = 1 AND
      PatientDoctor.PrimaryFlag = 1 AND

Note that your checks for alpha characters in the WHERE clause can be simplified to:


left(<column>, 1) not like '[a-zA-Z]'

but the first one would eliminate PatientID='X' and the last condition (AND PatientID = 'X') only accepts patient X. Both cannot be true at the same time, or am I misreading this?

macdca44
Starting Member

10 Posts

Posted - 2015-02-18 : 08:48:54

I want the query to give me all patients whether or not they have a course or an oncologist, so the oncologost row would be blank if they had no primary oncologist, therefore all outer joins are required but seems to give me duplicate rows. One which is OK and one where oncologist is blank. I only want a blank row for oncologist of there was none assigned?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 08:58:24

quote:
Originally posted by macdca44

I want the query to give me all patients whether or not they have a course or an oncologist, so the oncologost row would be blank if they had no primary oncologist, therefore all outer joins are required but seems to give me duplicate rows. One which is OK and one where oncologist is blank. I only want a blank row for oncologist of there was none assigned?

You say that all OUTER joins are required, but the query you posted is not using OUTER joins.

So, change my query to use LEFT JOINS and see what you get. If you seem to get duplicate rows, then take the query apart and look at the results with no joins, with the first join, with the second and so on. This will show you where the extra rows come in which will help you understand why and how to filter them out.

macdca44
Starting Member

10 Posts

Posted - 2015-02-18 : 09:03:58

This is more like what im trying to do:

SELECT DISTINCT

Patient.PatientId,
Patient.PatientSer,
Patient.CreationDate,
CHI=Patient.PatientId2,

Oncologist=(Select (Doctor.AliasName) FROM Doctor
WHERE Patient.PatientSer = PatientDoctor.PatientSer
AND PatientDoctor.ResourceSer = Doctor.ResourceSer
AND PatientDoctor.OncologistFlag = 1 and PatientDoctor.PrimaryFlag = 1 )

FROM Patient

LEFT OUTER JOIN PatientDoctor
ON Patient.PatientSer = PatientDoctor.PatientSer

LEFT OUTER JOIN Doctor
ON PatientDoctor.ResourceSer = Doctor.ResourceSer

WHERE

( Upper(Substring(Patient.PatientId,1,1)) not in ('A' , 'B' , 'C' , 'D' , 'E' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') )

ORDER BY Patient.PatientId ;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 10:36:07

OK (please use my simplification in the WHERE clause). What do you get with your modified query?

macdca44
Starting Member

10 Posts

Posted - 2015-02-19 : 11:17:45

I think that gives me what I need, thanks you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources