12 Replies - 1384 Views - Last Post: 02 January 2009 - 03:14 PM Rate Topic: -----

#1 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Database Query Problem?

Posted 30 December 2008 - 01:52 PM

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

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 an sql 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

Is This A Good Question/Topic? 0
  • +

Replies To: Database Query Problem?

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Database Query Problem?

Posted 30 December 2008 - 01:58 PM

View Postanix, on 30 Dec, 2008 - 12:52 PM, said:

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

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 an sql 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


Let me see if I have this. You are trying to look at the name of the patients that have been seen by the same doctors as patient P26?

This post has been edited by P4L: 30 December 2008 - 02:03 PM

Was This Post Helpful? 0
  • +
  • -

#3 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Re: Database Query Problem?

Posted 30 December 2008 - 02:03 PM

View PostP4L, on 30 Dec, 2008 - 12:58 PM, said:

View Postanix, on 30 Dec, 2008 - 12:52 PM, said:

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

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 an sql 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


Let me see if I have this. You are trying to look at the name of the patients that have been seen by the same doctors as patient P26?




Yes thats basically what i want to do, the information will prob come from the is_seen_by table but im not sure how to go about this one. thanks
Was This Post Helpful? 0
  • +
  • -

#4 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Database Query Problem?

Posted 30 December 2008 - 02:08 PM

Here's a question I should have asked. Is this in MS Access?
Was This Post Helpful? 0
  • +
  • -

#5 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Re: Database Query Problem?

Posted 30 December 2008 - 02:10 PM

no its a sql database
Was This Post Helpful? 0
  • +
  • -

#6 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Database Query Problem?

Posted 30 December 2008 - 02:14 PM

ok try this. You need a nested query



Select p.Surname
From Patient as p, Is_Seen_By as i
Where i.PatientCode = p.PatientCode
and i.DoctorCode= (Select d.DoctorCode
From Patient as p, Doctor as d, Is_Seen_By as i
Where i.DoctorCode = d.DoctorCode
and i.PatientCode = p.PatientCode
and p.PatientCode = 'P26') 



This should get you on the right track.
Was This Post Helpful? 0
  • +
  • -

#7 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Re: Database Query Problem?

Posted 30 December 2008 - 02:21 PM

Thanks for your help, im not sure how this will work as where you have put:

Select p.Surname
From Patient as p, Is_Seen_By as i

As i understand sql queries it will select the surrname from the patient table and store it in a p variable, and select the surname from the is_seen_by table and store it in a i variable, but here is the problem the is_seen_by table has no surname column. If i am getting this wrong im sorry lol!
Was This Post Helpful? 0
  • +
  • -

#8 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Re: Database Query Problem?

Posted 30 December 2008 - 02:28 PM

I think that this should work:

SELECT Patient.PatientSurname
FROM Patient
WHERE Is_Seen_By.PatientCode = Patient.PatientCode
AND Is_Seen_By.DoctorCode = (SELECT Is_Seen_By.DoctorCode
FROM Is_Seen_By
WHERE PatientCode = P26);


I am guessing that this should do the trick for me what do u think?
Was This Post Helpful? 0
  • +
  • -

#9 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Database Query Problem?

Posted 30 December 2008 - 02:32 PM

Umm...I do believe that is incorrect. What p and i do is called aliasing the table, or in other words, giving the name a temporary name. You then are joining the two tables together with this:
Where i.PatientCode=p.PatientCode


This say only to look for the SurName from the patient table where the PatientCodes from the table i and p are the same.

View Postanix, on 30 Dec, 2008 - 01:28 PM, said:

I think that this should work:

SELECT Patient.PatientSurname
FROM Patient
WHERE Is_Seen_By.PatientCode = Patient.PatientCode
AND Is_Seen_By.DoctorCode = (SELECT Is_Seen_By.DoctorCode
FROM Is_Seen_By
WHERE PatientCode = P26);


I am guessing that this should do the trick for me what do u think?

In essence, I say the same thing, just use another name for the table.
Was This Post Helpful? 0
  • +
  • -

#10 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Re: Database Query Problem?

Posted 30 December 2008 - 02:36 PM

I think i understand what you are saying, so do u think the way i have written it is ok and will work? thanks
Was This Post Helpful? 0
  • +
  • -

#11 anix  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 12-December 07

Re: Database Query Problem?

Posted 30 December 2008 - 03:55 PM

So is there any chance any1 can help me by telling me if my way of performing the query will work. thanks
Was This Post Helpful? 0
  • +
  • -

#12 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Database Query Problem?

Posted 31 December 2008 - 03:58 AM

I think your query is also correct.

This is wt i tried


select p.surname from patient as p inner join is_seen_by is on p.PatientCode=is.PatientCode   where is.doctorcode=(select  DoctorCode from is_seen_by where PatientCode='P26')


Was This Post Helpful? 0
  • +
  • -

#13 Muntz59  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 02-January 09

Re: Database Query Problem?

Posted 02 January 2009 - 03:14 PM

You should probably use the IN clause instead of =, just in case the sub-query returns more than one record.

 AND Is_Seen_By.DoctorCode IN (SELECT Is_Seen_By.DoctorCode 

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1