Using JOIN SQL queries

Lets say you need to pull data from two or more joined tables to show on the List/View/Edit page. The data should be searchable and editable (except for the joined fields that cannot be updated).

In this example we use two tables:

Cars

Users

Here is the default SQL query PHPRunner builds for the Cars table:

select [ID],
[Make],
[Model],
[UserID],
From [Cars]

Modify it like so:

select Cars.[ID],
[Make],
[Model],
[UserID],
UserName
From [Cars]
inner join users on cars.userid=users.id

If you don't specify which table the ID field belongs to, an error message similar to this one appears:

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.

See also:

•SQL Query screen

•Open Database Connectivity (ODBC)