|
|
Query Designer gives you the ability to use the simple graphical
interface to construct SQL statements.
 Note: if you
modify default SQL query, make sure that key column(s) are included
into fields list. This is required to provide edit/delete
functionality.
To switch between tables use Tables list panel on the left. Note
that all fields marked with a tick in the Output column are added to the SELECT
clause.
What is
supported
Inner joins, outer joins
To add join click the Add
Table button, select table and then drag and drop any field
from first table to the joined table. To setup join type click the
line between tables, select foreign keys on the Table link properties dialog in both
tables and choose join type.
More info about join
types.
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
To add calculated fields use empty grid below all field
names:
SQL query:
|
SELECT
category,
color,
Date Listed,
descr,
EPACity,
EPAHighway,
features,
UserID,
YearOfMake,
zipcode,
Price*0.1 AS Discount
FROM carscars
|
WHERE clause
You can add where clause in the Filter column. If you need to add two
or more conditions use Or... columns.
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 clause
If you'd like to specify default sorting order on the list page
(ascending or descending) select Sort Type in the corresponding column
for necessary fields.
GROUP BY clause
To add GROUP BY clause click the Group By button and select one of
grouping function in Group
By column.
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
|
|
