Joe Celko s SQL for Smarties - Advanced SQL Programming P40. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 362 CHAPTER 17 THE SELECT STATEMENT SELECT class_nbr class_size MIN room_size FROM Rooms Classes WHERE GROUP BY class_nbr class_size This will give a result table with the desired room sizes but not the room numbers. You cannot put the other columns in the SELECT list since it would conflict with the group by clause. But also note that the classroom with 85 seats r4 is used twice once by class cl and then by class c2 Result class_nbr class_size MIN room_size c1 80 85 room r4 c2 70 85 room r4 c3 65 70 c4 55 65 c5 50 55 c6 40 50 Your best bet after this is to use the query in an exists clause. SELECT FROM Rooms Classes WHERE EXISTS SELECT class_nbr class_size MIN room_size FROM Rooms Classes WHERE GROUP BY class_nbr class_size However some versions of SQL will not allow a grouped subquery and others will balk at an aggregate function in an exists predicate. The only way I have found to rectify this is to save the results to a temporary table then join it back to the Cartesian product of Rooms and Classes. Putting the columns for Rooms into the select list of the same query schema can do the second T-Join SELECT room_nbr room_size MAX class_size FROM Rooms Classes WHERE GROUP BY room_nbr room_size Dr. Codd s T-Join 363 This time the results are the same as those Dr. Codd got with his procedural algorithm Result room_nbr room_size MAX class_size r4 rl r6 r7 r3 85 70 65 55 50 80 65 55 50 40 If you do a little arithmetic on the data you find that we have 360 students and 395 seats 6 classes and 7 rooms. This solution uses the fewest rooms but note that the 70 students in class c2 are left out completely. Room r2 is left over but it has only 40 seats. As it works out the best fit of rooms to classes is given by changing the matching rule to less than or equal. This will leave the smallest room empty and pack the other rooms to capacity thus SELECT class_nbr .
