t-SQL - HELP PLEASE

[ecka00]

I need a resultset for a patient ID where physician type = 2 for a give record---however, if there are no physicians where type = 2 then i need to return the physician assigned to the patient where type = 0. please see script below


SELECT BED_STATUS_CD,
ROOM_MSTR.NURSING_STATION_ID,
ROOM_MSTR.ROOM_ID,
ROOM_MSTR.BED_ID,
PATIENT.LAST_NAME
AS PATIENT_NAME,

MEDREC.MEDREC_NO,
ROOM_MSTR.VISIT_NO,
PHYS_MSTR.LAST_NAME,
PHYSICIAN.PHYSICIAN_TYPE_CD

FROM PATIENT FULL OUTER JOIN
MEDREC RIGHT OUTER JOIN
VISIT ON MEDREC.URN = VISIT.URN LEFT OUTER JOIN

PHYSICIAN LEFT OUTER JOIN
PHYS_MSTR ON PHYSICIAN.PHYS_ID = PHYS_MSTR.PHYS_ID ON VISIT.VISIT_NO = PHYSICIAN.VISIT_NO FULL OUTER JOIN
ROOM_MSTR ON MEDREC.LOCATION_CD = ROOM_MSTR.LOCATION_CD AND VISIT.VISIT_NO = ROOM_MSTR.VISIT_NO ON
PATIENT.URN = VISIT.URN

WHERE (ROOM_MSTR.LOCATION_CD = @FACILITY )
AND (PHYSICIAN.PHYSICIAN_TYPE_CD = '2')

[mikr0s]

--the following will list all the visit_no's that are assigned to physician_type_cd = 2 .
If there is no physician_type_cd = 2 then a physician_type_cd = 0 having the same visit no will be displayed.....

--if you combine the following with your query, probably you will get what you want....

SELECT vis.VISIT_NO, phys2.PHYSICIAN_TYPE_CD
FROM VISIT vis
LEFT JOIN PHYSICIAN phys ON phys.VISIT_NO = vis.VISIT_NO AND PHYSICIAN_TYPE_CD = '2'
LEFT JOIN PHYSICIAN phys2 ON phys2.VISIT_NO = vis.VISIT_NO AND phys2.PHYSICIAN_TYPE_CD = ISNULL(phys.PHYSICIAN_TYPE_CD, '0')
WHERE phys2.PHYSICIAN_TYPE_CD IS NOT NULL


--HTH--

[ecka00]

DUDE, you are a SQL GOD!!! thank you so much. How can I learn more about including values in a JOIN? What should I reference--I haven't seen this before???