Database Query Problem??

diamondrajoor · 06-16-2009

Hi i have got a database with the following tables below:

Code:

Patient (
     PatientCode, PatientSurname, PatientFirstname, PatientSex, Age,
     PatientOccupation, PatientHeight, PatientWeight, PatientAddress
) 

Doctor (
     DoctorCode, DoctorSurName,  DoctorFirstName,
     DoctorPrivateAddress, MobileNo, Function
) 

Operation (
     Operation Code, PatientCode, DoctorCode, Date,
     Time, Result, OperationType
) 

Is_Seen_By (
     PatientCode, DoctorCode, Date, Time
)

Basically i am trying to design a query that will say allow me to find the surname of the patients that have been seen by at least one of the doctors that have seen patient "P26", I am not sure on what would be the most efficient way of doing this can any1 help? thanks

Shadab · 06-16-2009

Which RDBMS are you using. Oracle, MySQL, MS-SQL ?
Anyway :

Code:

SELECT PatientSurname
FROM Patient, Is_Seen_By
WHERE
   Patient.PatientCode = Is_Seen_By.PatientCode
   AND Is_Seen_By.DoctorCode IN
   (
      SELECT DoctorCode FROM Is_Seen_By WHERE PatientCode = "P26"
   )

06-16-2009	#1
diamondrajoor Newbie Join Date: Jun 2009 Posts: 3	Database Query Problem?? Hi i have got a database with the following tables below: Code: Patient ( PatientCode, PatientSurname, PatientFirstname, PatientSex, Age, PatientOccupation, PatientHeight, PatientWeight, PatientAddress ) Doctor ( DoctorCode, DoctorSurName, DoctorFirstName, DoctorPrivateAddress, MobileNo, Function ) Operation ( Operation Code, PatientCode, DoctorCode, Date, Time, Result, OperationType ) Is_Seen_By ( PatientCode, DoctorCode, Date, Time ) Basically i am trying to design a query that will say allow me to find the surname of the patients that have been seen by at least one of the doctors that have seen patient "P26", I am not sure on what would be the most efficient way of doing this can any1 help? thanks

06-16-2009	#2
Shadab Web Developer Join Date: Feb 2007 Location: Bhopal Posts: 2,739 iTrader: 100% (1)	Re: Database Query Problem?? Which RDBMS are you using. Oracle, MySQL, MS-SQL ? Anyway : Code: SELECT PatientSurname FROM Patient, Is_Seen_By WHERE Patient.PatientCode = Is_Seen_By.PatientCode AND Is_Seen_By.DoctorCode IN ( SELECT DoctorCode FROM Is_Seen_By WHERE PatientCode = "P26" ) My periodic table contains only 1 element : the element of Surprise.