Using a nested select query in Oracle 11g

Question

I'm trying to use a nested select and have it display rows with a patients first name patients last name and their email.

What I want to use is this:

select pat_first, pat_last, pat_email
from (select pat_id
  from patients
minus
  select pat_allergies_id_fk
  from patient_allergies);

However the patients table has pat_id, pat_first, pat_last, and pat_email. The Patient_allergies table only has patient_allergies_id_fk and patient_allergies columns. If I put the tables that I want into the first of the inner selects like:

select pat_first, pat_last, pat_email
from (select pat_id, pat_first, pat_last, pat_email
  from patients
minus
  select pat_allergies_id_fk
  from patient_allergies);

but it gives an error - "ORA-01789: query block has incorrect number of result columns". I figure it's because I'm trying to do a minus from a table with 5 columns and subtracting a table of two columns, but I'm not sure how I can get the query to display my desired columns (pat_first, pat_last, and pat_email).

For example, the patients table has 50 rows, and the patient_allergies table has 15, I'm trying to get those 35 rows not in patient_allergies and have the pat_first, pat_last, and pat_email rows outputted.

MaxU - stand with Ukraine · Accepted Answer · 2016-04-10 18:44:18Z

1

try this:

select pat_first, pat_last, pat_email
from patients
where pat_id not in (select pat_allergies_id_fk from patient_allergies);

or

select pat_first, pat_last, pat_email
from patients
where not exists (select null from patient_allergies where pat_allergies_id_fk = pat_id);

answered Apr 10, 2016 at 18:44

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2444400 Over a year ago

Awesome! That worked, thanks! I wasn't aware of the not in function (as I'm a novice at SQL).

Collectives™ on Stack Overflow

Using a nested select query in Oracle 11g

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related