JOINs problem

I work for a hospital network (three different hospitals involved), and
I need to generate a result set containing ever combination of
specialty and department applicable for each doctor. The base tables
are as follows...
CREATE TABLE Hospitals
(
HospitalID VARCHAR(10)
)
CREATE TABLE Specialties
(
DoctorID VARCHAR(10),
SpecialtyID VARCHAR(10),
)
CREATE TABLE Departments
(
DoctorID VARCHAR(10),
HospitalID, VARCHAR(10),
DepartmentID VARCHAR(10)
)
INSERT Hospitals VALUES ('HOSP1')
INSERT Hospitals VALUES ('HOSP2')
INSERT Hospitals VALUES ('HOSP3')
INSERT Specialties VALUES ('JONES1', 'CARDIO1')
INSERT Departments VALUES ('JONES1', 'HOSP1', 'CARVASSUR1')
INSERT Departments VALUES ('JONES1', 'HOSP3', 'VASSUR1')
Here's the kicker: specialties apply to all hospitals, while
departments are hospital-specific. Therefore, for Dr. Jones the
following should be produced...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 CARDIO1 CARVASSUR1
JONES1 HOSP2 CARDIO1 null
JONES1 HOSP3 CARDIO1 VASSURG1
...but if no specialty record existed for Dr. Jones, only the two
relevant hospitals would be represented...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 null CARVASSUR1
JONES1 HOSP3 null VASSURG1
What would be the simplest way to accomplish this result?how about:
SELECT *
FROM Hospitals as h
LEFT JOIN Departments as d on d.HospitalID = h.HospitalID
LEFT JOIN Specialties as s on s.DoctorID = d.DoctorID
WHERE NOT (s.DoctorID is null AND d.DoctorID is null)|||That would work *except* that where there is no specialty, I only want
a record for each hospital that has a related department record.|||so when you run my example, what output is it that's wrong, could you
give an example input, output, and desired output.|||Well, after discussing it with the client further, it appears that they
*would* like to see a record for each hospital, so your suggestion will
work for me after all. Thank you very much for your time.|||Glad it's all sorted, but I'm still pretty sure that the result will
actually show as you originally specified. You see if there's no
speciality, and no dept in the hospital, both these clauses will be
null, so it will be filtered by that where clause.
Anyway, as long as it's working, it's - just check that example
before you use it.
Cheers
Will