Lab Using Database Joins: Part 11

Constructing a Table of Faculty, Offices, and Labs

Approach C: Joining with offices and with labs, each declared as renamed tables

This approach largely follows work by Ted Cooper and Christine Gerpheide (with minor editing of variable names and formatting

logging into MySQL database: done

selecting database: done

defining query: SELECT first, last, t2.roomNumber AS labRoom, t2.phone AS labPhone, t3.roomNumber AS officeRoom, t3.phone AS officePhone FROM (SELECT first, last, facultyID FROM faculty2008) AS t1 LEFT JOIN (SELECT roomNumber, facultyID, phone FROM room JOIN facultyRoom ON room.roomNumber = facultyRoom.roomID WHERE roomType = 2 OR roomType = 3) AS t2 ON t2.facultyID = t1.facultyID LEFT JOIN (SELECT roomNumber, facultyID, phone FROM room JOIN facultyRoom ON room.roomNumber = facultyRoom.roomID WHERE roomType = 1) AS t3 ON t1.facultyID = t3.facultyID ORDER BY last, first;

executing query: done

Faculty or Staff Name		Office	Office	Lab Number	Lab Phone
First	Last	Number	Phone	(if any)	(if any)
Soren	Berg
Marge	Coahran	3827	3127
Janet	Davis	3809	4306	3807	4796
Samuel	Rebelsky	3824	4410	3823
John	Stone	3829	3181	3830	4990
Henry	Walker	3811	4208	3812	4340
Jerod	Weinman	3825	9812	3830	4990

closing database: done