Operations on Data Selection: Logical Conjunctions

 Introduction
 So far, we have stated the conditions one at a time. This made their interpretation easier. Sometimes, you will need to test one condition that depends on another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true. For a real estate company, suppose you have a customer who is considering purchasing a single family house around Silver Spring, Maryland, you would check the listing of the properties and find out what you have. To respond to this request, you must examine two conditions for each property:

The property must be a single family

• The property must be located in Silver Spring

When preparing your listing prior to seeing the customer, you can start by building a query that lists only the single family houses:

The other condition requires that the house be located in Silver Spring:

From these two results, notice that there is no relationship between the fact that a property is a single family that it is located in Silver Spring.

 AND a Logical Conjunction

A logical conjunction is a Boolean operation that combines at least two conditions. For example our customer wants to purchase a property only if two conditions are met: The property must be a single family AND the property must be located in Silver Spring. This type of condition is referred to as logical conjunction.

To create a logical conjunction, if you are working on a table in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, under Filter, type each condition as seen above. Here is an example:

If you are working in an ADO.NET graphical application, you can access the Properties window for the binding source and, in the Filter field, type the expression. Alternatively, you can access the variable of the binding source in your code and assign it the AND expression. In both cases, the expression must follow the Transact-SQL rule,

To create a logical conjunction in SQL, you use the AND operator. To write the statement, you use the the following formula:

```SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2```

The WhatColumn(s) and the WhatObject factors are the same we have used in previous WHERE conditions so far. The AND keyword is the operator that joins the conditions. Each condition is written as a SQL operation using the formula:

`Column operator Value`

In this case, the WHERE operator resembles the C#'s if conditional statement. The Condition1 is the first that would be examined. If the first condition is false, the whole statement is false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined.

Based on this, to get a list of properties that satisfies our customer, that is, to get a list that includes the single family properties in Silver Spring, we would create a SQL statement as follows:

```SELECT PropertyNumber,
City,
State,
ZIPCode,
PropertyType,
Condition,
Bedrooms,
Bathrooms,
Stories,
YearBuilt,
MarketValue
FROM   Properties
WHERE  PropertyType = 'Single Family' AND City = 'Silver Spring';```

In your Windows application, you can assign this expression to the binding source. Here is an example:

```private void btnAnalyze_Click(object sender, EventArgs e)
{
bsProperties.Filter =
"PropertyType = 'Single Family' AND City = 'Silver Spring'";
} ```

This would produce:

We stated that each condition was separately evaluated. For this reason, to make the conjunction statement easier to read, each condition should be included in parentheses. Therefore, the above SQL statement can be written as follows:

```SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM   Properties
WHERE  (PropertyType = 'Single Family') AND (City = 'Silver Spring');```

This would produce:

When creating a logical conjunction, you can apply all the other features we have reviewed so far:

• Because SQL is not case sensitive, you can write AND in either uppercase or lowercase
• Because SQL is not case sensitive, you can write the conditions in uppercase, lowercase, or a combination of cases
• You can negate a condition by preceding it with the NOT operator
• You can create alias names for the columns. Here are examples:
 ```SELECT PropertyNumber AS [Prop #], PropertyType AS Type, YearBuilt AS [Year Built], City, State, ZIPCode AS [ZIP Code], Bedrooms AS Beds, Bathrooms AS Baths, MarketValue AS Value FROM Properties WHERE (PropertyType = 'Single Family') AND (City = 'Silver Spring'); GO```
• You can use an alias name for the table:
 ```SELECT prop.PropertyNumber AS [Prop #], prop.PropertyType AS Type, prop.YearBuilt AS [Year Built], prop.City, prop.State, prop.ZIPCode AS [ZIP Code], prop.Bedrooms AS Beds, prop.Bathrooms AS Baths, prop.MarketValue AS Value FROM Properties prop WHERE (prop.PropertyType = 'Townhouse') AND (prop.MarketValue < 400000) GO```

Instead of one logical conjunction, you can create as many conjunctions as you want. To do this, use as many combinations of AND operators as necessary. For example, suppose a customer wants to purchase a single family in MD but she is not willing to spend over 500000. To get this list, you would need two AND conditions. You would create the condition as follows:

```SELECT PropertyNumber, PropertyType, City, Bedrooms, MarketValue
FROM   Properties
WHERE  PropertyType = 'Single Family' AND
State = 'MD' AND
MarketValue < 500000```

To make the WHERE condition easier to read, each expression should be included in its own parentheses. Here is an example:

```SELECT PropertyNumber,
City,
State,
ZIPCode,
PropertyType,
Condition,
Bedrooms,
Bathrooms,
Stories,
YearBuilt,
MarketValue
FROM   Properties
FROM   Properties
WHERE  (PropertyType = 'Single Family') AND
(State = 'MD') AND
(MarketValue < 500000)```

If you are working in a Windows application, you can pass the expression to the binding source. Here is an example:

```private void btnAnalyze_Click(object sender, EventArgs e)
{
bsProperties.Filter = "(PropertyType = 'Single Family') AND " +
"(State = 'MD') AND " +
"(MarketValue < 500000)";
} ```

This would produce:

 AND a Range BETWEEN

If you have a logical range of values and you want to know if a certain value is contained in that range, you can use add a BETWEEN operator to a WHERE statement. The BETWEEN operator is combined with AND to get a list of records between two values. The basic formula of this operator is:

`WHERE Expression BETWEEN Start AND End`

The Expression placeholder is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. The End factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End.

To create a BETWEEN expression in the Table window, under the Filter column that corresponds to the field on which you want to set the condition, type the BETWEEN expression. Here is an example that specifies the price range a customer wants to consider for a house:

As stated already, in SQL, you use the BETWEEN operator to create this type of condition. Here is an example:

```SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM Properties
WHERE MarketValue BETWEEN 320000 AND 450000```

To make the condition easy to ready, you should include the whole BETWEEN statement in parentheses. Here is an example:

When creating your BETWEEN statement, you can use any of the SQL features we have studied so far. To create a complex expression, you can combine many (or all) of the conditions we have reviewed so far. For example, you may have a customer who is considering purchasing a townhouse around Silver Spring but he does not want to spend more than 400000. Because the customer wants the townhouse in the Silver Spring area, you will not consider the City column. You remember that Silver Spring contains many ZIP Codes from 20800 to 20999. To get the list, you would formula your SELECT statement as follows:

```SELECT PropertyNumber AS [Prop #],
PropertyType AS Type,
YearBuilt AS [Year Built],
City,
ZIPCode AS [ZIP Code],
Bedrooms AS Beds,
Bathrooms AS Baths,
MarketValue AS Value
FROM Properties
WHERE (PropertyType = 'Townhouse') AND
(MarketValue < 400000) AND
(ZIPCode = (SELECT ZIPCode
WHERE ZIPCode BETWEEN '20500' AND '21000'));```

If you are working in a Windows application, you can assign the expression to the Filter property of the binding source.

Published on Friday 04 January 2008