3. Conditions

We can use WHERE condition to filter the records.

SELECT wo.WORKORDERID "Request ID",
       std.STATUSNAME "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE std.STATUSNAME = 'open'

Operators in WHERE clause:

= Equal

<> Not equal. Note: In some versions of SQL this operator may be written as !=

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

AND and OR Operators:

The WHERE clause can be combined with AND and OR operators.

The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND are TRUE.

(cd.CATEGORYNAME = 'Hardware' AND std.STATUSNAME = 'open')

The OR operator displays a record if any of the conditions separated by OR is TRUE.

(cd.CATEGORYNAME = 'Hardware' OR std.STATUSNAME = 'open')

Combination of AND and OR:

((cd.CATEGORYNAME = 'Hardware' AND std.STATUSNAME = 'open') OR (ti.FIRST_NAME = 'Howard'))

LIKE Operator

The LIKE operator can be used in the conditional selection of the where clause. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

(cd.CATEGORYNAME LIKE '%Hardware%')

WHERE cd.CATEGORYNAME LIKE 'a%' - Finds any values that start with "a"

WHERE cd.CATEGORYNAME LIKE '%a' - Finds any values that end with "a"

WHERE cd.CATEGORYNAME LIKE '%are%' - Finds any values that have "are" in any position

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

cd.CATEGORYNAME IN ('hardware','printer','software')