CODEWITHSUNDEEP
sqloracle-database
sumaaa
sumaaa

Reputation: 5

SQL to get data from 3 tables

I need to write an sql query to show the wing_name, sister_name and nurse_name by joining tables. 

SELECT WING.WING_NAME, SISTER.SISTER_NAME, NURSE.NURSE_NAME
  FROM NURSE
      JOIN SISTERNURSE ON NURSE.NURSE_ID = SISTERNURSE.NURSE_ID
      JOIN SISTER ON SISTER.SISTER_ID = SISTERNURSE.SISTER_ID
      JOIN WING ON WING.SISTER_ID = SISTER.SISTER_ID
WHERE WING.WING_NAME = '*';

Can anyone see what's wrong with this code?

I need to pull Nurse_name from the Nurse table, which is linked to another table called Sister by the foreign key Sister_ID, this table is then linked to another table called Wing which has the foreign key Sister_ID. SisterNurse is just a bridge table with the foreign keys Nurse_id and Sister_id.

I have four values in wing_name. Would I just put WING.WING_NAME= 'SPARROW', 'LORIKEET', 'MACAW', 'KINGFISHER'; ?

Table structure:

Sister table - Sister_id (primary key), sister_name, sister_surname, sister_contactnumber, sister_salary

Wing Table - wing_id(primary key), wing_name, number_of_rooms, sister_id(foreign key)

Nurse table - Nurse_id(primary key), nurse_name, nurse_surname, nurse_contactnumber, nurse_salary

SisterNurse table - SisterNurse_ID(primary key), Sister_id(foreign key), Nurse_id(foreign key)

Upvotes: 0

Views: 84

Answers (2)

Tushar
Tushar

Reputation: 3623

As you have commented on another answer below; I am adding those names in In Operator.

Please check the output:

 SELECT WING.WING_NAME, SISTER.SISTER_NAME, NURSE.NURSE_NAME
      FROM NURSE
          JOIN SISTERNURSE ON NURSE.NURSE_ID = SISTERNURSE.NURSE_ID
          JOIN SISTER ON SISTER.SISTER_ID = SISTERNURSE.SISTER_ID
          JOIN WING ON WING.SISTER_ID = SISTER.SISTER_ID
    WHERE WING.WING_NAME in ('SPARROW', 'LORIKEET', 'MACAW', 'KINGFISHER');

Upvotes: 1

MeshBoy
MeshBoy

Reputation: 692

use a proper value to wing_name and all other sql queries are seems to be ok. You enter a proper value for wing_name and execute the query. Check if it is still giving the same error or not.

Upvotes: 0

Related Questions