Home

Operations on Data Selection: Logical Disjunctions

 

Introduction

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:

  • The property is a single-family
  • The property is a townhouse

When creating the list, you would want to include a property only if it is either a single family or a townhouse:

  1. If the property is a single family, our statement is true and we don't need to check the second condition
  2. If the property is not a townhouse, then we consider the second condition. If the property is a townhouse, our statement becomes true

This type of statement is referred to as logical disjunction.

OR a Logical Disjunction

To create a logical disjunction, if you are working on a table:

  1. Under the first Or column, click the box that corresponds to one of the columns that will be involved in the OR condition, and type the necessary value or expression
  2. Under the second Or column, access the box that corresponds to the same column as previously and type the appropriate value or expression

Here is an example:

Logical Disjunction 

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:

Logical Disjunction 

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.

IN a Selected Series

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:

IN

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:

IN

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.