I am suppose to write the following query: Give the patient name, patient’s doct

Question

I am suppose to write the following query:
Give the patient name, patient’s doctor name, insurance name and insurance telephone number for patient number = 101

Here are my sql statements for tables
[PATIENT
CREATE TABLE PATIENTS
(Fname VARCHAR (15), Lname VARCHAR (15), PatientId CHAR(3), PatientAddress VARCHAR (30), ActiveAppointment DATE,
PayStatus VARCHAR(30), MedicalRecord VARCHAR(50), InsuranceType VARCHAR(25), InsurancePhoneNum CHAR(10)

VISIT
CREATE TABLE VISIT
(VisitId INT, Appointment INT, Diagnosis VARCHAR (150), Treatment VARCHAR(150), BillforProcedure VARCHAR (50), Cost INT);

APPOINTMENTS
CREATE TABLE APPOINTMENTS
(AppointmentID INT, PatientID INT, DoctorID INT, AppointmentDate DATE, Scheduled CHAR, AppointmentKept CHAR);

DOCTOR
CREATE TABLE DOCTOR
(DoctorID CHAR(8), DoctorLname VARCHAR(15), DoctorFname VARCHAR(15), DoctorActiveApp DATE, VisitHistory VARCHAR(150));

BILL
CREATE TABLE BILL
(BillID CHAR(8), RefVisit DATE, PaymentStatus VARCHAR(30));]

i need to write ONE SQL Statement...what i had was
SELECT Lname, Fname, PatientId, DoctorLname, DoctorFName, InsuranceName, InsurancePhoneNum,
FROM PATIENT, DOCTOR
WHERE Pat_Num = '101';

but i was counted wrong because i created the crossproduct of the table...i need to join the tables. Can anyone give me the sql statement for the above given query:
Give the patient name, patient’s doctor name ,
insurance name and insurance telephone number for
patient number = 101

NOTE: Patient number 101 is fake...i haven't filled out the tables yet.i just need the query.
Thanks

Explanation / Answer

Hi there,

The reason you get a cross product is because the PATIENTS and DOCTOR tables aren't related (no keys in common). You need to include a 3rd table to link them together -- Appointments would work. PATIENTS has a PATIENTID that will link to APPOINTMENTS.PATIENTID. APPOINTMENTS has a DOCTORID that links to DOCTOR.DOCTORID. Now you've linked all 3 tables (Patient -> Appointment -> Doctor).

The SQL could look something like:

SELECT P.Lname, P.Fname, D.DoctorLname, D.DoctorFName, P.InsuranceType, P.InsurancePhoneNum
FROM PATIENTS P, APPOINTMENTS A, DOCTOR D
WHERE P.PATIENTID = A.PATIENTID AND
A.DOCTORID = D.DOCTORID AND
P.PATIENTID = '101' AND
A.APPOINTMENTDATE = TO_DATE('07-NOV-2011');

You'll see I added an appointment date filter as well. Without it, you'd get all appointments for that patient. Even as written you're not guaranteed a single record -- ie. if the patient had more than one appointment on the specified day.

In any case, it won't result in a cross product since we're joining all 3 tables.

Hope this helps - let me know if you have any questions.

Joe

PS. The above join syntax was performed on an Oracle DB -- be sure to verify it on runs on yours as well.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.