MACROMEDIA COLDFUSION 5-DEVELOPING Manuale Utente Scaricare il pdf (Pagina 27)

ColdFusion MX 7 Getting Started Experience Tutorial Page 27 of 47

Learning Point: Joining data between database tables

The statement you created selects all of the artists and all of the art, but it does not assign the correct artist to the art that

they created.

To correctly assign an artist to their artwork, you need to explicitly tell the database how to connect the data. See the

relationships diagram:

Figure 43. Database

relationships diagram

The ARTISTID from the ARTISTS table, the ARTID from the ART table, and the MEDIAID from the MEDIA table are the

primary keys for those tables. Primary keys are unique identifiers, usually unique numbers, that make it easy for the

database to quickly identify information.

The line between the ARTISTS and ART table joins the two tables in the relationships diagram. The join uses the

ARTISTID primary key from the ARTISTS table to associate the art information from that table with the correct artwork in

the ART table. See the following figure:

Figure 44. Joining the Art

and Artists tables

Looking at the row of information in the ART table with an ARTID value of 8, you can see that the ARTNAME for the piece is

Dude and the ARTISTID associated with it is number 3. If you look at the ARTISTS table and locate the ARTISTID

number 3 from that table, you can see that the artist’s name is Elicia Kim.

When you store a copy of another table’s primary key in a second table, as you did for the ARTISTID in the ART table,

that key is referred to as a foreign key and is used to join data from the primary table to the secondary table.

7. Joins are denoted in SQL statements using a SQL WHERE clause. Add the WHERE clause to your index.cfm page to

ensure that the correct artist is always assigned to the correct art piece. Your code should now look like this:

SELECT FIRSTNAME, LASTNAME, ARTNAME, DESCRIPTION, PRICE, LARGEIMAGE, ISSOLD

FROM ARTISTS, ART

WHERE ARTISTS.ARTISTID = ART.ARTISTID

1 2 ... 22 23 24 25 26 27 28 29 30 31 32 ... 46 47

Nessun commento

MACROMEDIA COLDFUSION 5-DEVELOPING Manuale Utente Pagina 27