|
Lets say you need to pull data from two or more joined tables to
appear on List/View/Edit and other pages. Data should be searchable
and editable (except for joined fields that cannot be updated).
In this example we use two tables: Cars and Users
|
Cars
|
ID |
Make |
Model |
UserID |
|
1 |
Acura |
NSX-T |
1 |
|
2 |
Ford |
Crown Victoria |
2 |
|
3 |
Volkswagen |
Passat |
1 |
|
4 |
Toyota |
Avalon |
2 |
|
5 |
Audi |
TT |
3 |
|
|
Users
|
ID |
Username |
|
1 |
Bob |
|
2 |
Admin |
|
3 |
Bill |
|
4 |
Tina |
|
Here is the default SQL query ASPRunnerPro builds for table Cars
select [ID],
[Make],
[Model],
[UserID],
From [Cars]
Modify it the following way:
select Cars.[ID],
[Make],
[Model],
[UserID],
UserName
From [Cars]
inner join users
on cars.userid=users.id
If you don't specify what table
ID field belongs you see the error message similar to this one:
Error message:
[Microsoft][ODBC Microsoft Access
Driver] The specified field '[ID]' could refer to more than one table
listed in the FROM clause of your SQL statement.
|
