Combining Conditions when OR Has Precedence

Combining Conditions when OR Has Precedence

To link conditions with OR and give them precedence over conditions linked with AND, you must repeat the AND condition for each OR condition.

For example, imagine that you want to find all employees who have been with the company more than five years and have lower-level jobs or are retired. This query requires three conditions, a single condition linked to two additional conditions with AND:

• Employees with a hire date earlier than five years ago, and
  
  
• Employees with a job level of 100 or whose status is "R" (for retired).

The following procedure illustrates how to create this type of query in the Grid pane.

To combine conditions when OR has precedence

1. In the Grid pane, add the data columns you want to search. If you want to search the same column using two or more conditions linked with AND, you must add the data column name to the grid once for each value you want to search.
   
   
2. Create the conditions to be linked with OR by entering the first one into the Criteria grid column and the second (and subsequent ones) into separate Or ... columns. For example, to link conditions with OR that search the job_lvl and status columns, enter values as shown here:

Entering the values shown in the grid above produces the following WHERE clause in the statement in the SQL pane:

WHERE (job_lvl = 100) OR (status = 'R')

3. Create the AND condition by entering it once for each OR condition. Place each entry in the same grid column as the OR condition it corresponds to. For example, to add an AND condition that searches the hire_date column and applies to both OR conditions, enter values as shown here:

Entering the values shown in the grid above produces the following WHERE clause in the statement in the SQL pane:

WHERE (job_lvl = 100) AND 
  (hire_date < '01/01/90' ) OR
  (status = 'R') AND 
  (hire_date < '01/01/91' )

Tip   You can repeat an AND condition by adding it once, and then using the Cut and Paste commands from the Edit menu to repeat it for other OR conditions.

The WHERE clause created by the Query Designer is equivalent to the following WHERE clause, which uses parentheses to specify the precedence of OR over AND:

WHERE (job_lvl = 100 OR status = 'R') AND
   (hire_date < '01/01/91')

Note   If you enter the search conditions in the format shown immediately above in the SQL pane, but then make a change to the query in the Diagram or Grid panes, the Query Designer recreates the SQL statement to match the form with the AND condition explicitly distributed to both OR conditions.

See Also

Combining Conditions when AND Has Precedence | Combining Search Conditions | Comparison Operators | Specifying Search Criteria