|
|
On the SQL tab you can
modify SQL query manually. All changes on this tab automatically
transfer to the Query Designer.
 Note: if you
modify default SQL query, make sure that key column(s) are
included. This is required to provide edit/delete functionality. If
table participates in Master-Details relationship make sure link
fields (primary/foreign key) stay on the list of fields.
To switch between tables use Tables list panel on the left.
What is
supported
Inner joins, outer joins
SQL query:
|
SELECT
carsmodels.id,
carsmodels.model,
carsmodels.make
FROM carsmodels
INNER JOIN carsmake ON carsmodels.make = carsmake.make
|
Note: it's
recommended to use aliases for fields from joined tables to avoid
confusion when two fields from different tables have the same
name.
Calculated fields
SQL query:
|
SELECT
category,
color,
Date Listed,
descr,
EPACity,
EPAHighway,
features,
UserID,
YearOfMake,
zipcode,
Price*0.1 AS Discount
FROM carscars
|
In the example above the alias Discount is assigned to the calculated
field Price*0.1. Note that
if the field was assigned an alias in the SQL query, then the
$values array will get the
alias instead of field name from the database. So you should use
$values["Discount"]
instead of $values["Price*0.1"] in your events.
For more information about events, see Events.
We do not recommend using aliases to give a field another name.
If you have very long or complex field names, you can assign a
label to the field on Choose fields
page or in the Lable editor instead
of using aliases.
WHERE clause
SQL query:
|
SELECT *
FROM carscars
WHERE YearOfMake =2004
|
For more complicated queries wrap the condition by
parentheses:
|
SELECT *
FROM carscars
WHERE ( YearOfMake =2004 OR YearOfMake =2005 )
|
ORDER BY and GROUP BY
clauses
SQL query:
|
SELECT
Make,
Model,
AVG (YearOfMake)
FROM carscars
GROUP BY Make, Model
ORDER BY Make
|
What is not
supported
|
·
|
Update/Delete/Insert/Create queries
|
|
