Exercise 6: Join Many Tables

Hopefully you're getting your head around selecting data out of tables. Always remember this: SQL ONLY KNOWS TABLES. SQL LOVES TABLES. SQL ONLY RETURNS TABLES. TABLES. TABLES. TABLES. TABLES! I repeat this in this rather crazy manner so that you will start to realize that what you know in programming isn't going to help. In programming, you deal in graphs and in SQL you deal in tables. They're related concepts, but the mental model is different.

Here's an example of where it becomes different. Imagine you want to know what pets Zed owns. You need to write a SELECT that looks in person and then "somehow" finds Zed's pets. To do that you have to query the person_pet table to get the id columns you need. To do that you need to either join the three tables (person, person_pet, pet) together with equality expressions, or you have to do a second select to return the correct pet.id numbers.

Here's how I'd do it with a join:

View Source file ex6.sql Only