Multiple Conditions

You can add multiple criteria to your Where clauses by using "and" or "or."

Syntax:

Select */fieldname<mask> ... 
from tablename ... 
where fieldname =/!=/<> ... value
and/or
fieldname =/!=/<>... value
and/or
fieldname =/!=/<> ... value 
order by fieldname <desc> ...

Explanation:

• The and tells SQL to retrieve the record if both conditions are met.
• The or tells SQL to retrieve the record if either condition is met.
• The or is less restrictive and retrieves more records.
• If multiple ands and ors are used, the ands are evaluated first, and then the ors.
• Use parentheses to change precedence (the order of evaluation).

Examples:

Retrieve all vendors who are located in Chicago and have a current balance over 1,500 dollars. Order the results by current balance.

1. In the SQL text box, type:

Select Name, City, CurrBal [-ZZ,ZZZ.99]
from TrnVendor
where City = 'Chicago'
and CurrBal > 1500
order by CurrBal;

2. Execute the SQL statement.

Results:
 

Name	City	CurrBal
Make Shift Tilts	Chicago	1,793.23
Music Maker	Chicago	4,109.14

---

Retrieve all vendors who are located in Chicago or who have a current balance over 1,500 dollars.

1. In the SQL text box, type:

Select Name, City, CurrBal [-ZZ,ZZZ.99]
from TrnVendor
where City = 'Chicago'
or CurrBal > 1500
order by CurrBal;

2. Execute the SQL statement.

Name	City	CurrBal
Distant Horizons	Chicago	26.98
BedMakers Linen	Chicago	500.00
Paltry Play	Chicago	1,000.00
Spot Out	Detroit	1,606.54
Narrow Nest	Toledo	1,609.00
No Waste Disposal	Park Ridge	1,720.98
Make Shift Tilts	Chicago	1,793.23
Legal Lookup	Houston	3,215.16
Music Maker	Chicago	4,109.14
Softer Software	Monroe	9,873.67

---

The next two SQL statements use exactly the same syntax except that the order of precedence is different (note parentheses in second example).

Retrieve all vendors from Chicago whose expense account is 4110 or any vendor with a current balance over 1200.

1. In the SQL text box, type:

Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct
from TrnVendor
where City = 'Chicago'
and ExpAcct = '4110'
or CurrBal < 1200 
order by City, ExpAcct;

2. Execute the SQL statement.

City	CurrBal	ExpAcct
Bayshore	498.05	4110
Burlington	51.00	4120
Chicago	4,109.14	4110
Chicago	500.00	4120
Chicago	1,000.00	6080
Chicago	26.98	7230
Dallas	0.00	4030
Dallas	20.00	6080
Detroit	1,001.23	7230
Freeport	642.98	4110
Freeport	771.06	6510
Mercer	321.70	6080
Mercer	-109.23	7230
Monroe	342.98	4110
New York	617.00	6080
Orlando	0.00	4110
Orlando	64.72	4110
Park Ridge	967.24	6510
Toledo	23.45	4030
Toledo	42.89	6510
Toledo	13.13	7230
Warren	453.17	4110
Youngstown	345.09	7230

---

Retrieve all vendors whose expense account is 4110 or who have a current balance over 1200. The vendor must be located in Chicago.

1. In the SQL text box, type:

Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct
from Vendor
where City = 'Chicago'
and (ExpAcct = '4110'
or CurrBal > 1200) 
order by City, ExpAcct;

2. Execute the SQL statement.

Results:
 

City	CurrBal	ExpAcct
Chicago	0.00	4030
Chicago	4,135.24	4110
Chicago	1,309.95	4110
Chicago	100.00	6010
Chicago	500.00	6040
Chicago	47.00	7010
Chicago	106.00	7190

Previous Page Table of Contents Next Page