Skip to content
This repository
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

file 72 lines (58 sloc) 3.362 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
\chapter{Exercise 6: Select Across Many Tables}

Hopefully you're getting your head around selecting data out of tables. Always
remember this: \emph{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 \ident{SELECT} that
looks in \ident{person} and then "somehow" finds my pets. To do that
you have to query the \ident{person\_pet} table to get the \ident{id}
columns you need. Here's how I'd do it:

\begin{code}{ex6.sql}
<< d['code/ex6.sql|pyg|l'] >>
\end{code}

Now this looks like a lot, but I'll break it down so you can see it's
simply crafting a new table based on data in the three tables and the
\ident{WHERE} clause:

\begin{description}
\item[ex6.sql:1] I only want some columns from \ident{pet} so I specify them
    in the select. In the last exercise you used '*' to say "every column"
    but that's going to be a bad idea here. Instead, you want to be explicit
    and say what column from each table you want, and you do that by using
    \ident{table.column} as in \ident{pet.name}.
\item[ex6.sql:2] To connect \ident{pet} to \ident{person} I need to go
    through the \ident{person\_pet} relation table. In SQL that means I
    need to list all three tables after the \ident{FROM}.
\item[ex6.sql:3] Start the \ident{WHERE} clause.
\item[ex6.sql:4] First I connect \ident{pet} to \ident{person\_pet} by the
    related id columns \ident{pet.id} and \ident{person\_pet.id}.
\item[ex6.sql:5] \emph{AND} I need to connect \ident{person} to \ident{person\_pet}
    in the same way. Now the database can search for only the rows where the id
    columns all match up, and those are the ones that are connected.
\item[ex6.sql:6] \emph{AND} I finally ask for only the pets that I own by adding a
    \ident{person.first\_name} test for my first name.
\end{description}

\section{What You Should See}

When you run this you should get this exactly, not any of the data you've
entered in previous exercises:

\begin{code}{ex6.sql Output}
<< d['code/ex6.sh-session|pyg|l'] >>
\end{code}

If you didn't, then do a \ident{SELECT} on the \ident{person\_pet} table and
make sure it's right. You might have inserted too many values into it.


\section{Extra Credit}

\begin{enumerate}
\item This may be a mind blowing weird way to look at data if you already know
    a language like Python or Ruby. Take the time to model the same relationships
    using classes and objects then map it to this setup.
\item Do a query that finds your pets you've added thus far.
\item Change the queries to use your \ident{person.id} intead of the
    \ident{person.name} like I've been doing.
\end{enumerate}

\section{Portability Notes}

There are actually other ways to get these kinds of queries to work called
"joins". I'm avoiding those concepts for now because they are \emph{insanely}
confusing. Just stick to this way of joining tables for now and ignore people
who try to tell that this is somehow slower or "low class".
Something went wrong with that request. Please try again.