CODEWITHSUNDEEP
sqloracle-databasejoin
donlaur
donlaur

Reputation: 1287

Oracle JOIN on 3 tables with 2-3 lookups

I am not sure what types of JOINs that I will need.

I have one table that is people. The people table has id, lastname, firstname, title, departmentid, and phone. The second table is department. department table has id, name, roomid, and telephone. The third table is peoplerooms. peoplerooms just has the peopleid and roomid fields. The fourth table is rooms. rooms has id, name, and roomnumber.

The goal is to query people and return people.id, lastname, firstname, title, name (from the department table using the departmentid), and also room.name using peoplerooms to find all rooms that the peopleid has.

people table
- id 
- lastname
- firstname
- title
- departmentid
- phone

sample people data
1|smith|will|director|2|555-555-5555
2|doe|jane|director|3|555-333-4545

peoplerooms table
- peopleid
- roomid

sample peoplerooms data
1|10
2|5

rooms table
- id
- name
- roomnumber

sample rooms data
3|Room C|100C
5|Room B|100B
10|Room A|100A

department table
- id
- name
- roomid
- phone

sample department data
3|Radiology|5|555-454-4444
2|Billing|3|656-555-6656

I have the following SQL working but I am not pulling in the department name... I am only getting the people.departmentid and I need to lookup department and instead get the department.name.

SELECT people.lastname, people.firstname, people.departmentid, people.title, room.roomnumber, people.phone  
FROM room 
JOIN peopleroom ON room.id = peopleroom.roomid 
JOIN people ON peopleroom.peopleid = people.id 
ORDER BY people.lastname

Upvotes: 0

Views: 65

Answers (1)

timo.rieber
timo.rieber

Reputation: 3867

You are right on the way. Just join department as well. Try the following query. I've added people.id and room.name, because you mentioned it in your question:

SELECT
    people.lastname,
    people.firstname,
    people.title,
    people.phone,

    room.roomnumber,
    room.name AS roomname,

    department.name AS departmentname

FROM
    room JOIN peopleroom ON room.id = peopleroom.roomid
    JOIN people ON peopleroom.peopleid = people.id
    JOIN department ON people.departmentid = department.id

ORDER BY
    people.lastname

Upvotes: 2

Related Questions