Crestron CT-3000 Spezifikationen PDF herunterladen (Seite 34)

Crestron e-control Database Manager SW-DBM

34 •• The Database File Installation & Reference Guide — DOC. 5823

• The Albums table contains a row for each album available to the

application. The columns of the Albums table include the artist, and the

genre, the name of the album, and a unique Album ID.

• The Tracks table contains a row for each track of each album from the

Albums table. The columns of the Tracks table include the track number,

track name, and playing time of the track, and the Album ID of the album in

the Albums table where the track is found.

The objective is to make all the album information available when querying for a

track. Because each album can have a different number of tracks, you would not try

to put all the track information in the album record. An alternative is to create

separate records in the Albums table for each track. However, doing so requires

duplicating all the album information in the records for each and every track on the

album.

The solution is to keep the tables as they are, and separate, but let the database

engine appear to “join” them when you access the track table. We do this by

indicating the tables to join, and the “pivot field,” the field they have in common that

ties the information from the superior table to each and every record in the

subordinate table.

In this case, the pivot field is the Album ID. This join creates a recordset with a

record for each track that contains additional columns for selected fields from the

Albums table. In the following figure, the album title appears in each track record.

A portion of the results of a join query viewed from Microsoft Access

Following is a Queries table record that might produce the above query:

Hypothetical Queries table record, viewable from either the server or from Microsoft Access.

NOTE: The list fields, also included in the generated query, are not shown in the results above.

The value in the table column shown above specifies that matching rows for the

Albums and Tracks table are those rows that have the same values in the CDJID

column (A.CDJID = T.CDJID). This is an INNER JOIN, meaning that only rows

with a match in both tables are retrieved and displayed. If an album in the Albums

table has no matching track rows in the Tracks table, that album is not included in

the records retrieved and sent to the touchscreen. Similarly, if tracks exist with no

matching rows in the Albums table, they are also ignored.

The SQL statement constructed from this row of the Queries table is:

SELECT

T.Track,

T.Title,

A.Artist AS ARTIST,

A.Title AS ALBUM,

T.Track AS CUT,

T.Title AS TUNE,

1 2 ... 29 30 31 32 33 34 35 36 37 38 39 ... 93 94

Keine Kommentare

Crestron CT-3000 Spezifikationen Seite 34