SQL query help, conditional join

Question

Hi guys, SQL newbie here :)

Here are my tables if anyone's interested.

AHH, cant post image yet http://img832.imageshack.us/img832/442/72098588.jpg

What I'm trying to do is query the tblPatientStats table within a date interval (@StartDate, @EndDate) and group them accordingly in a data grid on winforms.

So each row in tblPatientStats either have a RefDoctor or RefMode or both or none at all.

So the query should return a table with the Name of the patient from tblPatient, the RefMode from tblRefMode, the Name of the RefDoctor (Title + FirstName + lastName) and SessionDate from tblPatientStats

==> yfrog dot com/0yhi2dj

Here is my attempt so far.

INSERT @Final(Name, Doctor, Mode, SessionDate)
 SELECT DISTINCT (FirstNames + LastName) as Name, 
 (tblRefDoctor.RefDTitle + ' ' + tblRefDoctor.RefDFNames + ' ' + tblRefDoctor.RefDName) AS Doctor, 
 tblRefMode.RefMode AS Mode, SessionDate 

 FROM tblPatientStats, tblPatient
 left outer join tblRefDoctor on (RefDoctor = tblRefDoctor.RefDoctor)
 left outer join tblRefMode on (RefModeID = tblRefMode.RefModeID)
 WHERE
 tblPatientStats.RefDoctor IS NOT NULL or tblPatientStats.RefModeID IS NOT NULL
 AND 
 tblPatient.PatientID = tblPatientStats.PatientID
 AND tblPatientStats.SessionDate between @StartDate AND @EndDate 

What am I doing wrong? The query times out every single time, the tables are small, less than 10K records each.

Any help would be much appreciated.

Answer 1

I suspect the issue is because of the cartesian join on

tblPatientStats, tblPatient

Whilst there is a join condition in the where clause there is an issue with the precedence of the boolean operators. This is in order Not, And, Or so I think you need brackets around the 'Or' ed conditions.

The WHERE condition on the original query with brackets applied to show the effective operator precedence is

 WHERE
 tblPatientStats.RefDoctor IS NOT NULL or 

(tblPatientStats.RefModeID IS NOT NULL 
     AND tblPatient.PatientID = tblPatientStats.PatientID  
     AND tblPatientStats.SessionDate between @StartDate AND @EndDate)

This is almost certainly not the desired semantics and will likely bring back too many rows.

I've moved the join condition between tblPatientStats and tblPatient up into the JOIN clauses and added brackets to the Or ed conditions.

 FROM tblPatientStats
 inner join tblPatient on tblPatient.PatientID = tblPatientStats.PatientID
 left outer join tblRefDoctor on RefDoctor = tblRefDoctor.RefDoctor
 left outer join tblRefMode on RefModeID = tblRefMode.RefModeID
 WHERE
 (tblPatientStats.RefDoctor IS NOT NULL or tblPatientStats.RefModeID IS NOT NULL)
 AND tblPatientStats.SessionDate between @StartDate AND @EndDate