|
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.
What
is supported
To add join click on 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 on the line
between tables, select foreign keys on the Table
link properties dialog in both tables and choose join type.
<more about join types here>
SQL query:
SELECT
products.ProductID,
products.ProductName,
products.SupplierID,
products.CategoryID,
products.UnitPrice,
categories.CategoryName
FROM products
INNER JOIN categories ON
products.CategoryID = categories.CategoryID
Note:
it's recommended to use aliases for fields from joined tables to
avoid confusion when two from different tables have the same name.
To add calculated fields use empty grid below all field names:
SQL query:
Select ProductName,
Quantity,
UnitPrice,
UnitPrice*0.05 as Tax
from Products
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 customers where country='UK'
select * from customers where (country='UK' or customertype='USA')
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.
To add GROUP BY clause click "Group By" button and select
one of groupping function in Group By column.
SQL query:
Select ProductName,
CategoryID,
SUM(UnitPrice)
from Products
GROUP BY ProductID, ProductName, CategoryID
ORDER BY ProductName
What is not supported
|
