TSQL Returning one Row when expecting multiple

Question

I have two tables, a patient table an and appointment table. I'm attempting to retrieve the information from both tables depending on what doctor is logged in at the time. My stored procedure is as follows:

ALTER PROCEDURE dbo.sprocGetAllAppointmentsForUser
@UserID varchar(50)
   AS
SELECT Appts.appt_id, Appts.patient_id, Appts.dr_id, Appts.appt_time, Appts.appt_reason, p.patient_id, 
p.patient_first_name, p.patient_last_name, p.patient_addr, p.patient_city, p.patient_zip,
p.patient_state, p.patient_phone, p.patient_healthcare, p.patient_diagnosis, p.patients_user_id
FROM Patients p INNER JOIN Appts
ON Appts.patient_id = p.patients_user_id
WHERE Appts.dr_id = @UserID
RETURN

That should get every appointment and patient information correct? Or am I over simplifying it. Heres some sample data:

Appointment Table:

appt_id        patient_id        dr_id
   1               467             101
   2               242             101
   3               784             210

Will only return the first row, even though there are two rows corresponding to dr_id 101.

Answer 1

The syntax looks sound, but are you sure you didn't mean p.patient_id instead of p.patients_user_id in the JOIN condition? Hard to tell for sure without seeing a database diagram though.

Answer 2

You'll get all the appointments (that have patients) for that doctor - you'll also get the patients in those appointments.

If an appointment doesn't have a patient, it will not appear.

If a patient doesn't have an appointment, it will not appear.

If a patient has 2 appointments with this doctor, the patient will appear twice.

If an appointment has 2 patients, the appointment will appear twice.

Answer 3

Just a rough guess

instead of Appts.patient_id = p.patients_user_id

try Appts.patient_id = p.patient_id

Answer 4

Are you positive all the patient ids you are looikng for actually exist in the patient table? Do you get a different result set if you change the join around like this:

FROM Patients p 
RIGHT JOIN ApptsON Appts.patient_id = p.patients_user_id
WHERE Appts.dr_id = @UserID

Answer 5

try investigating this in steps first

SELECT 
  *
FROM Appts
WHERE Appts.dr_id = 101

if that returns 2 rows the data is ok in your appts table

then add

SELECT
    a.patient_id
    ,p.*
FROM Appts A
LEFT JOIN Patients p 
    ON Appts.patient_id = p.patients_user_id
WHERE Appts.dr_id = 101

if you see null values returned in p.* part, then there is something wrong with your join condition, you may be joining on a wrong field. or the patient record for corresponding patient_id is missing in the Patients table