Joins from multiple tables

Transact-SQL
1

I have created the following query:

SELECT     PatientProfile.searchname as PatientName
 , PatientProfile.Birthdate as DOB
 , DoctorFacility.OrgName
 , Appointments.ApptStart
 , Appointments.ApptStop
 , ApptType.Name
FROM         PatientProfile left JOIN
                      DoctorFacility ON PatientProfile.DoctorId = DoctorFacility.DoctorFacilityId 
                      AND PatientProfile.FacilityId = DoctorFacility.DoctorFacilityId 
                      AND PatientProfile.PrimaryCareDoctorId = DoctorFacility.DoctorFacilityId 
                      AND PatientProfile.RefDoctorId = DoctorFacility.DoctorFacilityId 
                      right JOIN
                      Appointments ON DoctorFacility.DoctorFacilityId = Appointments.DoctorId 
                      AND DoctorFacility.DoctorFacilityId = Appointments.FacilityId 
                      AND DoctorFacility.DoctorFacilityId = Appointments.ResourceId 
                      right JOIN
                      ApptType ON Appointments.ApptTypeId = ApptType.ApptTypeId
                      
                      where appttype.Name like 'sbx%' or appttype.Name like 'obot%'

I am able to return the following results from columns ApptStart, ApptStop, Name, however patientname, patientbirthdate, and location do not contain any data which I also need. Since I am new to creating queries on multiple tables I am quite sure I am writing (linking) something wrong. Would appreciate any help offered.

2

Break the query up into smaller pieces for testing.
So what happens when you combine PatientProfile with DoctorFacility? Do you need that many criteria to be unique? Would not the FacilityID get you what you want?
Also I see that DoctorFacility.DoctorFacilityId is used to match to two columns in PatientProfile, is that what you want?

3

When I combine PatientProfile.Birthdate with DoctorFacility.Orgname I get no results on an inner join. If I change to left join I get the birthdate. However, I need to show both birthdate and location.

I do not wish to match two columns from PatientProfile to DoctorFacility. I used the query design editor to bring in the four tables so I think it automatically did the linking.

4

I have updated my query to:

DECLARE @StartDate DATETIME = '2014-11-1'

Select Distinct PatientProfile.PatientProfileID, PatientProfile.Birthdate, PatientProfile.Last, PatientProfile.First
,APPTTYPE.Name, DoctorFacility.OrgName, ApptSlot.Start, apptSlot.Stop

from PatientProfile, Appttype, DoctorFacility, ApptSlot

where ApptType.Name like 'sbx%' or ApptType.Name like 'obot%' and DoctorFacility.OrgName like 'Salem%'

order by patientprofile.last

I get the results, however there are too many rows (over 308,000). So it is just a matter of getting the joins right I believe. Again, if anyone can help I would greatly appreciate it.

5

Can someone help please?