Полезная информация

next up previous contents index
Next: Additional Join Possibilities Up: Joining Tables Previous: Performing Joins

Three and Four Table Joins

You can perform a three-table join as shown in figure [*].

  
Figure: Three-table join
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~~~~~~~~~~~~~~~~~~~~~~~~\vert Lee~Meyers
\par (1~row)\end{list}\par
\end{figure}

The first printed column is the customer name. The second column is the employee name. Both column are labeled name. You could use AS to give the columns unique labels. Figure  [*] shows a four-table join, using AS to make each column label unique.
  
Figure: Four-table join
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
... part\_name~~~~\vert Garage~Door~Spring~
\par (1~row)\end{list}\par
\end{figure}

Some field wrapping is shown in the output of the query. The psql \x option is used to re-display the results in a more readable format. The four-table join matches the arrows in figure [*], with the arrows of the salesorder table pointing to the other three tables.

Joins can be performed among tables that initially don't even appear to be related. Suppose you wish to find employees who have taken orders for each customer. Figure [*] shows such a query.

  
Figure: Employees who have taken orders for customers.
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\vert~~~~~1
\par (1~row)\end{list}\par
\end{figure}

Notice that the query displays just the customer and employee tables. The salesorder table is used to join the two tables but is not displayed. The DISTINCT keyword is used because multiple orders taken by the same employee for the same customer would make that employee appear more than once, which was not desired. The second query uses an aggregate to return a count for each unique customer, employee pair.


next up previous contents index
Next: Additional Join Possibilities Up: Joining Tables Previous: Performing Joins
Bruce Momjian
1999-11-21