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.

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.

NP! (Please mark the thread as closed.)