CODEWITHSUNDEEP
sqlselectoracle11g
user2444400
user2444400

Reputation: 139

Using a nested select query in Oracle 11g

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.

Upvotes: 0

Views: 891

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210982

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);

Upvotes: 1

Related Questions