Twist on Master-Detail

I have a rather difficult requirement, for me at least.

I have a Master table with multiple Detail tables. All these tables return just null or one record with the exception of one table that would return null or more [than one].

Joining and Left joining the tables preceding the Multi-detail table (didn't know what else to call it) is easy, straight forward stuff, but how would I retrieve multiple records? Have I finally found a reason to use a right join?

Without posting too many table scripts, here's the SP that pulls all the data:

SELECT 
Asclepius.GetFullPatientName(P.FirstNameAr,P.SecondName开发者_如何转开发Ar, P.ThirdNameAr, P.FourthNameAr) AS PatientName,
PA.AdmittanceID,
PInvoice.InvoiceSerialNo,
PInvoice.InvoiceTotal,
PInvoice.InvoiceID,
--EmergencyWard
EW.Wages, EW.Bandages,EW.ECG, EW.Equipment,EW.FirstAid,EW.MedicalSupplies,EW.Medicine,EW.Other,
--Maternity
M.Wage,M.Graphing,M.Equipment,M.Medicine,M.MedicalSupplies,M.MaternityOperatingRoom,M.MaternityOpeartionFees,M.Other,
--RoomService
RS.Stay,RS.Cohabitant,RS.Medicine,RS.MedicalSupplies,RS.Oxygen,RS.MonitorUsage,RS.ECG,RS.Telephone,RS.Cafeteria,RS.Other,
--Nursery
N.Bed,N.Incubator,N.LightIncubator,N.Medicine,N.MedicalSupplies,N.Equipment,N.Other,
--Lab And Radiology
LR.LabTests,LR.BloodBank,LR.Radiology,LR.Ultrasound,LR.CTScan,LR.Endoscopy,LR.MRI,LR.Catheter,LR.Other,
--Surgery
S.ORFees,S.MedicalSupplies,S.Medicine,S.Equipment,S.SurgeryShifts,S.Other,
--Doctors
D.DoctorID,D.Wage,CD.DoctorNameAr

FROM
--PatientInvoice
Accounting.PatientInvoice AS PInvoice
INNER JOIN EMR.Patient AS P ON PInvoice.PatientID = P.PatientID
INNER JOIN EMR.PatientAdmittance AS PA ON P.PatientID = PA.PatientID AND     PInvoice.AdmittanceID = PA.AdmittanceID
--EmergencyWard
LEFT JOIN Accounting.PatientInvoice_EmergencyWard AS EW ON PInvoice.PatientID =     EW.PatientID AND PInvoice.AdmittanceID = EW.AdmittanceID AND PInvoice.InvoiceSerialNo = EW.InvoiceSerialNo
--Maternity
LEFT JOIN Accounting.PatientInvoice_Maternity AS M ON PInvoice.PatientID = M.PatientID AND PInvoice.AdmittanceID = M.AdmittanceID AND PInvoice.InvoiceSerialNo = M.InvoiceSerialNo
--RoomService
LEFT JOIN Accounting.PatientInvoice_RoomService AS RS ON PInvoice.PatientID = RS.PatientID AND PInvoice.AdmittanceID = RS.AdmittanceID AND PInvoice.InvoiceSerialNo = RS.InvoiceSerialNo
--Nursery
LEFT JOIN Accounting.PatientInvoice_Nursery AS N ON PInvoice.PatientID = N.PatientID AND PInvoice.AdmittanceID = N.AdmittanceID AND PInvoice.InvoiceSerialNo = N.InvoiceSerialNo
--Lab And Radiology
LEFT JOIN Accounting.PatientInvoice_LabAndRadiology AS LR ON PInvoice.PatientID = LR.PatientID AND PInvoice.AdmittanceID = LR.AdmittanceID AND PInvoice.InvoiceSerialNo = LR.InvoiceSerialNo
--Surgery
LEFT JOIN Accounting.PatientInvoice_Surgery AS S ON PInvoice.PatientID = S.PatientID AND PInvoice.AdmittanceID = S.AdmittanceID AND PInvoice.InvoiceSerialNo = S.InvoiceSerialNo
--Doctors
LEFT JOIN Accounting.PatientInvoice_Doctors AS D ON PInvoice.PatientID = D.PatientID AND PInvoice.AdmittanceID = D.AdmittanceID AND PInvoice.InvoiceSerialNo = D.InvoiceSerialNo
LEFT JOIN EMR.ConsultantDoctors AS CD ON D.DoctorID = CD.DoctorID

The culprit being the last two join under the comment --Doctor. All the previous tables return just 0 or 1 record, that last table could provide 0 or n.

Or do Simply use MARS and retrieve that one independently?

TIA

---

Do you know for a fact that it is failing to return n rows as that is what a join is supposed to do? If the columns types match up you can use UNION.