How can Subquery return members of same team ?

[jisque]

Want to include a constraint in a CREATE TABLE statement for a table in a Hospital database, with these tables:

Team(TeamCode, TelNo, HeadNo)
Doctor (StaffNo, dName,Specialism,TeamCode)
Patient(PatientId, pName,ConsultantNo, WardNo,
AdmissionDate, DischargeDate)
Prescription(StaffNo, PatientId, StartDate, Prescription)

Only staff on the same team as the consultant responsible for a patient [consultantno in patient table]can offer treatment. Consultant is head of a team. This is what I've done so far but doesn't work:

CREATE TABLE Prescription
(PatientId CHAR(3),
StaffNo CHAR(3),
StartDate DATE NOT NULL,
Prescription VARCHAR(100) NOT NULL,
PRIMARY KEY (StaffNo,PatientId),
FOREIGN KEY (StaffNo) REFERENCES Doctor,
FOREIGN KEY (PatientId) REFERENCES Patient,
CHECK (StaffNo IN (select StaffNo
from Doctor
where Doctor.team_code =
(select TeamCode
from Team, Patient
where (HeadNo = ConsultantNo) And (PatientId =Patient.PatientId)))));

 

[r937]

run your check constraint's SELECT by itself, and please show the error message

select StaffNo
  from Doctor
 where Doctor.team_code =  
  ( select TeamCode
      from Team
         , Patient
     where (HeadNo = ConsultantNo) 
       And (PatientId =Patient.PatientId)
  )

i'm betting it will say "ambiguous column", and after you resolve that, i'm betting it will say "subquery must return only 1 value"

r937.com | rudy.ca