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

next up previous contents index
Next: Choosing a Join Key Up: Joining Tables Previous: Three and Four Table

Additional Join Possibilities

At this point, all joins have involved the salesorder table in some form. Suppose we wanted to assign an employee to manage each customer account. If we add an employee_id column to the customer table, the column could store the id of the employee assigned to manage the customer's account. Figure [*] shows how to perform the join between customer and employee tables.

  
Figure: Joining customer and employee
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
....employee\_id~=~24
\par test->~ORDER~BY~customer.name\end{list}\par
\end{figure}

The first query finds the employee name assigned to manage customer number 648. The second query shows the customer names managed by employee 24. Notice the salesorder table is not involved in this query.

Suppose you wanted to assign an employee to be responsible for answering detailed questions about parts. Add a employee_id column to the part table, place valid employee id's in the column, and perform similar queries as show in figure [*].

  
Figure: Joining part and employee
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~employee.employee\_id~=~24
\par test->~ORDER~BY~name\end{list}\par
\end{figure}

There are cases where a join could be performed with the state column. For example, to check state codes for validity8.4, a statecode table could be created with all valid state codes. An application could check the state code entered by the user, and report an error if the state code is not in the statecode table. Another example would be the need to print the full state name in queries. State names could be stored in a separate table and joined when the full state name is desired. Figure [*] shows an example of a statename table.

  
Figure: Statename table
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...D
\par test->~~~~~~~~customer.state~=~statename.state\end{list}\par
\end{figure}

This shows two more uses for additional tables:


next up previous contents index
Next: Choosing a Join Key Up: Joining Tables Previous: Three and Four Table
Bruce Momjian
1999-11-21