I'm having a data base which looks like this
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
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"
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.