Lab Using Database Joins: Part 11

Constructing a Table of Faculty, Offices, and Labs

Approach B: Using parallel record sets, one for offices and one for labs

logging into MySQL database: done

selecting database: done

defining query for offices:

Office Query: SELECT first, last, faculty2008.facultyID, roomNumber, phone FROM faculty2008 LEFT JOIN (facultyRoom INNER JOIN room ON facultyRoom.roomID=room.roomNumber AND roomType=1) ON faculty2008.facultyID=facultyRoom.facultyID ORDER BY last, first;

executing query: done

defining query for labs:

Lab Query: SELECT first, last, faculty2008.facultyID, roomNumber, phone FROM faculty2008 LEFT JOIN (facultyRoom JOIN room ON facultyRoom.roomID=room.roomNumber AND roomType=2) ON faculty2008.facultyID=facultyRoom.facultyID ORDER BY last, first;

executing query: done

Number of office records: 7
Number of lab records: 7

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

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