SQL - two difrent selects

[hatebin]

Hi,
I'm having a data base which looks like this

patient(pk_id)--(fk_pat)appointment(fk_doc)--(pk_id)doctor(fk_spe)---(pk_id)specialty

The goal is to select all the patients which had an appointment with a doctor with 'dentist' speciality.

I made 2 requests, one is correct and the other is incorrect

correct one:

select distinct patient.name
from pataient, appointment, doctor, specialty

where appointment.patient_fk=patient.id and
      appointment.doctor_fk = doctor.id and 
      doctor.specialty_fk = specialty.id and
      specialty.name='DENTIST';

The incorrect one:

select distinct name from patient where id=(
select patient_fk from appointment where doctor_fk=(
select id from doctor where specialty_fk=(
select id from specialty where name='DENTIST'
)));

When I use the second one there comes an outcome:

ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

Could some one please explain me the difrence between these two requests. Becouse I prefer making reqs like the second one.
Or can some on tell me how it suppose to be.

[hfx642]

The queries are close, but not quite the same.
Because of the FK = (sub-query), this can only return ONE row.
However, I'm sure that you have more than one doctor who is a dentist.
Therefore, you get... ORA-01427: single-row subquery returns more than one row
Changing your query to...

select distinct name from patient where id [B]IN[/B] (
select patient_fk from appointment where doctor_fk [B]IN[/B] (
select id from doctor where specialty_fk=(
select id from specialty where name='DENTIST'
)));

should get you the results you desire.
You also may wish to add...

Order by Upper (Name)

after all of your parentheses.

[hatebin]

Thanks a lot hfx.

[hfx642]

NP! (Please mark the thread as closed.)