 |
» |
|
|
|
You can now select and combine various columns of
information by joining tables
based on common column values. For example, suppose
you wanted to
see all album titles, selections, composer, and the medium.
This data is found in two tables, but can be joined by albumcode, as follows:
isql=>SELECT albumtitle, medium, selectionname, composernameReturn
>FROM music.albums, music.selection Return
>WHERE music.albums.albumcode = music.selections.albumcodeReturn
>AND music.albums.albumcost > 15.00; Return
|
This produces the following list:
Figure 6-10 JOINED List
select albumtitle, medium, selectionname, composername from music.albums
----------------------------------------+------+---------------------------
ALBUMTITLE |MEDIUM|SELECTIONNAME
----------------------------------------+------+---------------------------
Chopin Recital: Ivo Pogorelich |cd |Klaviersonate Nr. 2 - 3
Chopin Recital: Ivo Pogorelich |cd |Klaviersonate Nr. 2 - 4
Chopin Recital: Ivo Pogorelich |cd |Prelude cis-moll op 45
Chopin Recital: Ivo Pogorelich |cd |Scherzo cis-moll op 39
Chopin Recital: Ivo Pogorelich |cd |Etude F-dur op 10. No 8
Chopin Recital: Ivo Pogorelich |cd |Nocture Es-dur op 55 No 2
Chopin Recital: Ivo Pogorelich |cd |Klaviersonate Nr. 2 - 2
Chopin Recital: Ivo Pogorelich |cd |Klaviersonate Nr. 2 - 1
Chopin Recital: Ivo Pogorelich |cd |Etude gis-moll op 25. No 6
Chopin Recital: Ivo Pogorelich |cd |Etude As-dur op 10. No 10
---------------------------------------------------------------------------
Number of rows selected is 10
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] < e
|
AlbumTitle and Medium come from the Albums table, SelectionName and
ComposerName are from the Selections table. The WHERE clause defines the
join criteria: albumcode is matched from each table. We have further
qualified the query by asking only for albums costing more than $15.00.
|
