Help with expanding SQL Query 1

[dilvid]

ello all. Ive got an existing SQL query which I need to expand on. Currently I have 3 Tables Patient(PatientCode, PatientSurname, PatientFirstname), Doctor(DoctorCode, DoctorSurname, DoctorFirstName) and Operation(OpCode, PatientCode, DoctorCode, Date). What the current query I have does is find out the patient details of someone who had an operation on a certain date. But what I need it to do now is to get the patient details and doctor details. Any ideas on how to do this?? heres what I have on the original query so far

SELECT * From Patient Where PatientCode IN(SELECT PatientCode FROM Operation WHERE Date = '21/12/00');

Any help will be a big help!!

 

[DotNetGnat]

Try this:

SELECT t1.OpCode as OperationCode,t1.[Date] as OperationDate,
t2.PatientCode, t2.PatientSurname, t2.PatientFirstName,
t3.DoctorCode,t3.DoctorSurName, t3.DoctorFirstName FROM
Operation t1 INNER JOIN
Patient t2 ON t1.PatientCode=t2.PatientCode
INNER JOIN Doctor t3
ON t1.DoctorCode=t3.DoctorCode

-DNG

 

[dilvid]

Ahh that’s perfect apart from one thing, i only need the Patient and doctors name from as certain date. Sorry im not really used to SQL as im only doing this for a friend’s chiropody surgery as she’s actually taken on more chiropodists as she’s having a kid and cant work as many hours lol. She used to do everything but she can’t now so I offered to enhance her system so cope with the new staff lol. Anyway I don’t know if I edited bits I shouldn’t of but I got this but I get an error when I run it.

SELECT PatientSurname, PatientFirstname, DoctorSurname, DoctorFirstname FROM (SELECT t1.OperationCode as OperationCode,t1.Date as OperationDate, t2.PatientCode, t2.PatientSurname, t2.PatientFirstName,t3.DoctorCode,t3.DoctorSurName, t3.DoctorFirstName FROM Operation t1 INNER JOIN Patient t2 ON t1.PatientCode = t2.PatientCode INNER JOIN Doctor t3 ON t1.DoctorCode=t3.DoctorCode) WHERE OperationDate = '21/11/05';

The Error im getting is :

Error Code : 1248
Every derived table must have its own alias
(16 ms taken)

Any help, ive searched for the error and found a few pages and tried their examples and altered some code but nothing seem to help.

 

[DotNetGnat]

try this:

SELECT t1.OpCode as OperationCode,t1.[Date] as OperationDate, 
t2.PatientCode, t2.PatientSurname, t2.PatientFirstName,
t3.DoctorCode,t3.DoctorSurName, t3.DoctorFirstName FROM
Operation t1 INNER JOIN
Patient t2 ON t1.PatientCode=t2.PatientCode
INNER JOIN Doctor t3
ON t1.DoctorCode=t3.DoctorCode
WHERE t1.Date = '21/11/05'

-DNG

 

[dilvid]

ty, your a star!!! I was being held up by this (kinda stubborn and wouldnt move onto another part until it was done lol). Thank you