Retrieve a field of foreign key table
Syntax:
SELECT K.F,...... FROM T
Parameter:
|
T |
Table name |
|
K.F |
F field of the foreign key table referenced by table T’s foreign key K; K is foreign key name, or foreign key field name when it is a single-field key |
Example:
|
SELECT ID,PayDate,Amount,SupplierID.NAME FROM Payment |
Query records of the foreign key table using the syntax “foreign key field.field name” |
|
SELECT ID,PayDate,Amount,fk2.NAME FROM Payment |
Query records of the foreign key table using the syntax “foreign key name.field name” |
|
SELECT SupplierID,Name,ContactName,ContactTitle, SupplierID.TotalAMT FROM Supplier |
Get TotalAMT field from Supplier table and its homo-dimension table PaySupplierSum |
|
SELECT ID,PayDate,Amount, SupplierID.SupplierID.TotalAMT FROM Payment |
Query Payment table and get TotalAMT field from its foreign key table Supplier’s homo-dimension table PaySupplierSum |
Learning points:
1) A single-field foreign key can be named after the field name, but a composite foreign key should be named specifically.
2) Tables are homo-dimension tables between each other when the relationship between their primary keys constitutes foreign key association; a table’s homo-dimension table’s homo-dimension table is also its homo-dimension table; primary keys of homo-dimension tables must have a one-to-one correspondence in order.
3) You can reference a field in a corresponding homo-dimension table directly, but you should reference a field in a non-homo-dimension table recursively thorough the foreign key association.