Operation Manual

Understanding Databases
Linking tables
25
506 Crystal Reports User’s Guide
With this in mind, you can link the SalesRep table to the Manager table by the
Salary field in each table using a Greater Than link:
SELECT SalesRep.‘Last Name‘,
SalesRep.‘Salary‘,
Manager.‘Last Name‘,
Manager.‘Salary‘
FROM ‘SalesRep‘ SalesRep,
‘Manager‘ Manager
WHERE SalesRep.‘Salary‘ >
Manager.‘Salary‘
This SQL statement might produce data similar to this:
In this table, there is no relationship established between sales representatives
and sales managers. Since all managers have seniority over all sales
representatives, a company might find it necessary to check if any
representatives make more money than any managers, evidence of a salary
problem that needs to be remedied.
Greater Than Or Equal [>=] link
The result set from a Greater Than Or Equal link includes all records in which
the linked field value in the primary table is greater than or equal to the linked
field value in the lookup table. The example here is identical to the example
for the Greater Than join, except that it uses the Greater Than Or Equal link:
SELECT SalesRep.‘Last Name‘,
SalesRep.‘Salary‘,
Manager.‘Last Name‘,
Manager.‘Salary‘
FROM ‘SalesRep‘ SalesRep,
‘Manager‘ Manager
WHERE SalesRep.‘Salary‘ >=
Manager.‘Salary‘
SalesRep Table SalesRep Table Manager Table Manager Table
Last Name Salary Last Name Salary
Davolio $35,000.00 Fuller $32,000.00
Davolio $35,000.00 Brid $30,000.00
Davolio $35,000.00 Buchanan $29,500.00
Dodsworth $48,300.00 Hellstern $45,000.00
Dodsworth $48,300.00 Fuller $32,000.00
Dodsworth $48,300.00 Brid $30,000.00
Dodsworth $48,300.00 Buchanan $29,500.00
Dodsworth $48,300.00 Martin $35,000.00
Patterson $30,000.00 Buchanan $29,500.00