Operations on Data Selection: Logical Disjunctions
Suppose a customer is considering purchasing either a single family house or a townhouse. To prepare the list or properties, you must create a list that includes only these two options. Before building the query, you can state the following:
When creating the list, you would want to include a property only if it is either a single family or a townhouse:
This type of statement is referred to as logical disjunction.
To create a logical disjunction, if you are working on a table:
Here is an example:
If you are working in a Windows application, you can assign the logical disjunction to the Filter property of the binding source.
To create a logical disjunction in SQL, you use the OR operator. To do this, after the WHERE operator, type two conditions separated by an OR operator. The formula to use would be:
SELECT WhatColumn(s) FROM WhatObject WHERE Condition1 OR Condition2
Each condition must be complete; that is, it can consist of a Column=Value expression. Here is an example:
SELECT PropertyType, City, Bedrooms, Stories, MarketValue FROM Properties WHERE PropertyType = 'Single Family' OR PropertyType = 'Townhouse';
To make the disjunction easier to read, each expression should be included in parentheses. Here is an example:
SELECT PropertyType, City, Bedrooms, Stories, MarketValue FROM Properties WHERE (PropertyType = 'Single Family') OR (PropertyType = 'Townhouse');
This would produce:
When creating a disjunction, you can apply any of the other SQL features we have reviewed so far.
Instead of two disjunctions, you can create three or more if you want. To add one more disjunction if you are working on a table, in the third Or column, click the box that corresponding to the same column used for the previous Or condition, and type the desired value or expression.
If you have a series of records and want to find a record or a group of records among them, you can use the IN operator by adding it to a WHERE statement. The IN operator is a type of various OR operators. It follows this formula:
IN(Expression1, Expression2, Expression_n)
Each Expression factor can be one of the values of a column. This is equivalent to Expression1 OR Expression2 OR Expression_n, etc.
To create an IN expression in the Table window, select the desired columns. Under the Filter column that corresponds to the field on which you want to set the condition, type the IN expression. Here is an example:
To create an IN expression in SQL, after the WHERE operator, type the name of the column that holds the values to be considered, followed by the IN expression. From our list of students, imagine that you want to get a list of students who live either in Silver Spring, in Rockville, or in Chevy Chase. You can write an IN expression as follows:
SELECT FirstName, LastName, Sex, City, State FROM Students WHERE City IN ('Silver Spring', 'Rockville', 'Chevy Chase')
To make the expression easier to read, you should include the whole IN statement in its own parentheses. Here is an example:
When creating your IN statement, you can combine any of the other features we have so studied so far. For example, to get a list of single family and townhouses located in Rockville, you would create a statement as follows:
SELECT house.PropertyNumber AS [Prop #], house.PropertyType AS Type, house.YearBuilt AS [Year Built], house.City, house.State, house.ZIPCode AS [ZIP Code], house.Bedrooms AS Beds, house.Bathrooms AS Baths, house.MarketValue AS Value FROM Properties house WHERE (house.PropertyType IN ('Single Family', 'Townhouse')) AND (house.City = 'Rockville')
If you are working in a Windows application, you can assign the IN expression to the Filter property of the binding source.
Published on Friday 04 January 2008
|Home||Copyright © 2007 FunctionX, Inc.|