Contents

 
Home
PHPRunner 8.0 manual
Prev Page Next Page
 
 

Using JOIN SQL queries

 

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

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 PHPRunner 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.

Converted from CHM to HTML with chm2web Standard 2.85 (unicode)