Creating a Clause Using the SQL Statement Tree

Go Up to Using Query Builder

Query Builder lets you build more detailed WHERE, ORDER BY, GROUP BY, and HAVING clauses using the SQL Statement Tree. Query Builder lets you add clauses to SELECT, UPDATE, DELETE, and CREATE VIEW statements.

Note: Query Builder does not support clauses for INSERT statements.

Creating a WHERE Clause

Query Builder lets you create a WHERE clause from the SQL Statement Tree which automatically displays in your query.

Note: Any additional WHERE clauses are displayed as HAVING clauses.

The following table describes the options and functionality on the Where dialog box.

Option	Description
Operand (Left)	Lets you click the target column for the first part of your WHERE clause. Note: Query Builder lists every column in all tables in the SQL Diagram in the Operand lists.
Operator	Lets you select the target operator.
Operand (Right)	Lets you click the target column for the second part of your WHERE clause. Query Builder automatically writes the query language in the Statement option box.

Note: Query Builder does not display clause phrases created from the SQL Statement Tree in the SQL Diagram Pane.

To Create a WHERE clause

• Click the WHERE node, and then on the Query Builder tool bar, click New.

  OR

• Right-click the WHERE node, and then click New.

Deleting a WHERE Clause

To delete a WHERE clause

• Expand the AND node, and then on the Query Builder tool bar, click Delete.

  OR

• Expand the AND node, right-click target column, and then click Delete.

  Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating an AND Clause in a WHERE Clause

Query Builder lets you add an AND clause from the SQL Statement Tree which automatically displays in your query.

The following table describes the options and functionality on the Where dialog box.

Option	Description
Operand (Left)	Lets you click the target column for the first part of your WHERE clause.
Operator	Lets you select the target operator.
Operand (Right)	Lets you click the target column for the second part of your WHERE clause. Query Builder automatically writes the query language in the Statement option box.
New Button	Click to clear your selections but remain in the Where dialog box. Query Builder adds another AND clause to your query.

To open the Where dialog box

• Click the AND node, and then on the Query Builder tool bar, click New.

  OR

• Expand the WHERE node, right-click the AND node, and then click New.

Deleting an AND Clause

To delete an AND clause

• Expand the AND node, click target column, and then on the Query Builder tool bar, click Delete.

  OR

• Expand the AND node, click target column, and then on the keyboard press DELETE.

  OR

• Expand the AND node, right-click the target column, and then click Delete.

  Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Inserting an AND or OR Clause

Query Builder lets you insert an AND or an OR WHERE clause from the SQL Statement Tree which automatically displays in your query. Query Builder lets you insert AND or OR clauses at any appropriate point in the SQL Statement Tree.

The following table describes the options and functionality on the Where dialog box.

Option	Description
Operand (Left)	Lets you click the target column for the first part of your WHERE clause.
Operator	Lets you select the target operator.
Operand (Right)	Lets you click the target column for the second part of your WHERE clause. Query Builder automatically writes the query language in the Statement option box.
New Button	Click to clear your selections but remain in the Where dialog box. Query Builder adds another AND clause to your query.

To insert an AND or OR Clause

1. On the SQL Statement Tree, expand the WHERE node, right-click the target AND node,
2. Click Insert.
3. Click And or Or.

Deleting an OR Clause

To delete an OR clause

• Expand the OR node, and then on the Query Builder tool bar, click Delete.

  OR

• Expand the OR node, right-click the target column, and then click Delete.

  Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating an ORDER BY Clause

Query Builder lets you create an ORDER BY clause from the SQL Statement Tree which automatically displays in your query.

The following table describes the Order By Columns dialog box.

Option	Description
Available Columns	Select target column(s), and then click the right arrow. Query Builder moves target column from the Available Columns list to the Order By Columns list. Note: Query Builder sorts query results based on the order that columns are placed in the ORDER BY clause.
Order	Lets you select the target sort order: ASC. Ascending order. DESC. Descending order. Query Builder displays the SQL language in the Order By Statement box.

To open the Order By Columns dialog box

• On the SQL Statement Tree, click the ORDER BY node, and then on the Query Builder tool bar, click Properties.

  OR

• On the SQL Statement Tree, right-click the ORDER BY node, and then click Properties.

Changing the Sort Order in an ORDER BY Clause

To quickly change the sort order of a column in a query

1. On the SQL Statement Tree, expand the ORDER BY node, and then double-click the target column.

   OR

   On the SQL Statement Tree, expand the ORDER BY node, right-click the target column, and then click Properties.

   Query Builder opens the Order dialog box.

2. Click the target sort order, and then click OK.

   Query Builder appends the Order By clause for target column with the appropriate sort order in the SQL Statement Pane.

Deleting an ORDER BY Clause

To delete an ORDER BY clause

• Expand the ORDER BY node, and then on the Query Builder tool bar, click Delete.

  OR

• Expand the ORDER BY node, right-click the target column, and then click Delete.

Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating a GROUP BY Clause

The following table describes the options and functionality on the Group By Columns dialog box.

Option	Description
Selected Columns	Select target column(s), and then click the right arrow OR click Select All. Query Builder moves target column from the Selected Columns list to the Group By Columns list. Note: Query Builder sorts query results based on the order that columns are placed in the ORDER BY clause.
Clear All Button	Click to move target column from the Group By Columns list to the Selected Columns list. Query Builder displays the SQL language in the Group By Statement window.

To create a GROUP BY clause from the SQL Statement Tree which automatically displays in your query

1. On the SQL Statement Tree, double-click the GROUP BY node.

   OR

   On the SQL Statement Tree, right-click the GROUP BY node, and then click New.

   Query Builder adds all the selected columns in your table(s) to the GROUP BY node in the SQL Statement Tree, and to the appropriate location in the SQL Statement Pane.

2. On the GROUP BY node, double-click any column.

   OR

   On the GROUP BY node, click any column, then on the Query Builder menu, click New.

   OR

   On the GROUP BY node, right-click any column, then click Properties.

Deleting a GROUP BY Clause

To delete a GROUP BY clause

• On the SQL Statement Tree expand the GROUP BY node, and then on the Query Builder tool bar, click Delete.

  OR

• On the SQL Statement Tree Expand the GROUP BY node, right-click the target column, and then click Delete.

Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Creating a HAVING Clause

A HAVING clause is a type of WHERE clause. It filters additional information from your tables. Query Builder lets you create a HAVING clause from the SQL Statement Tree which automatically displays in your query. Query Builder lists every column in all tables in the SQL Diagram in the Operand lists. Query Builder displays the datatype of a column in the operand boxes.

The following table describes the options and functionality on the Having dialog box.

Option	Description
Operand (Left)	Lets you click the target column for the first part of your HAVING clause.
Operator	Lets you select the target operator.
Operand (Right)	Lets you click the target column for the second part of your HAVING clause. Query Builder automatically writes the query language in the Statement option box.
New Button	Click to clear your selections but remain in the Having dialog box. Query Builder adds another AND clause to your query.

Note: Query Builder does not display clause phrases created from the SQL Statement Tree in the SQL Diagram Pane.

To create a HAVING clause

• On the SQL Statement Tree, expand the HAVING node, and then expand the And node. If there is not a join listed on the And node, double-click And. If there is a join listed, use the following shortcut option.

  OR

• On the SQL Statement Tree, right-click the HAVING node, and then click New.

Deleting a HAVING Clause

To delete a HAVING clause

• On the SQL Statement Tree, expand the HAVING node, and then on the Query Builder tool bar, click Delete.

  OR

• On the SQL Statement Tree, expand the HAVING node, right-click the target column, and then click Delete.

Query Builder deletes the target clause and removes it from the SQL Statement Pane.

Changing Tables and Columns Location in the SQL Statement Tree

Query Builder lets you move tables and columns on the SQL Statement Tree by dragging them to new locations. You can move columns from the AND and OR nodes to an AND or OR node on the WHERE and HAVING clause nodes. Query Builder changes the query in the SQL Statement Pane to match each move. Query Builder moves tables or columns you are dragging below target table or column.

To move a table or column in the SQL Statement Tree

1. Expand target node, and then drag the target table or column to a new location.

   Query Builder makes the appropriate change in the query in the SQL Statement Pane.

   Note: Query Builder lets you select multiple tables or columns.

2. To move a table or column to the bottom of a node, drag it to the target node.

   Query Builder displays the target table or column at the bottom of target node.

Topics

• Selecting Columns in the SQL Statement Tree.