How do I get what I want from Query?

Anonymous Blackbaud User

Care must be taken to get exactly what you want from Query.

Query is one of the advanced tools in the product. Learning how to use Query requires some patience and practice. Below are the guidelines for beginners. For those experienced with SQL, you should know that Query is a (more or less) a simplified version. Operator precedence follows the same rules in Query as it does in SQL.

This is the heart of understanding how to use Query: "Query will pick out the records for which the expression is true."

This means that the query might look very different than the English language expression with similar meaning. For example, both of the following statements have the same meaning:

English: "I want everyone who lives in North Dakota and Idaho." The common understanding will be that denizens of those states are to be included in the result.

Query: "Home State/Prov equals ND OR Home State/Prov equals ID". Why the "OR"?

In the English version, we used "AND", but in Query, we used "OR". The reason for the difference is how the "operator" (AND/OR) is applied. Query works by searching the database, one contact record at a time. It applies all of the statements in the query, and if enough of them are true, adds the record to the result. Remember this. In English, the states are the focus of discrimination. In Query, it is the records.

Here are some examples:

1. Simple statements

Biographical First Name equals "Chris"

This says "If someone's first name is Chris, they belong in the result." "Christopher", "Christine" or "Christian" do not apply.

Biographical First Name begins with "Chris"

This will get anybody with a first name that starts with Chris, including the ones excluded earlier.

not a member of the group.

This gets everyone not in a particular group.

2. How "AND" works

AND says that both statements on either side must be true to consider the record for inclusion in the result.

Home State/Prov equals WV

AND Home State/Prov equals UT

This says, "If someone lives in West Virginia AND Utah, they belong in the result." Since it isn't possible to mark two states for Home Address in the db, this always returns nothing!

Home State/Prov equals New York

AND Home City equals Albany

This says "If someone lives in the state of New York and in the city of Albany, they belong in the result." This might seem redundant until you realize there is an Albany, GA and Albany, CA, and perhaps more.

Home Zip greater than 12008

AND Home Zip less than 14236

This gets everyone that lives in zip codes from 12008 and 14236. This is an extremely handy trick, but be careful! zip codes are not necessarily sequential on the map, so 12336 and 12337 are not necessarily adjoined.

3. How "OR" works

OR says only one statement on either side must be true to include the record in the result.

Home Zip greater than 12008

OR Home Zip less than 14236

This looks an awful lot like the previous example, but has the exact opposite effect. This query gets everyone in the database who have an American zip code.

Biographical First Name begins with B

OR Home State equals MA

This gets Barb in Nebraska, Bill in Texas, and everyone (including Ben and Jill) in Massachusetts.

4. How parenthesis () work

Parenthesis make clear to the database what order to evaluate things. Things in parenthesis are evaluated first.

Home City equals Albany

OR Home Zip equals 23561

OR Home City equals Omaha

AND Home State/Prov equals NY

The results of this query may surprise you. For example, you could assume it means this:

"Everyone who lives in New York AND also lives in Albany, Omaha, or the 23561 zip code."

...but that is wrong. What it does say is:

"Every in the 23561 zip code, everyone in any city named Albany, and everyone in Omaha, New York".

Why the difference? The first interpretation makes much more sense, if you are trying to isolate a group of New Yorkers. The difference is because the database interprets AND before OR. So it does the last connection first. This can be avoided by using parenthesis:

(Home City equals Albany

OR Home Zip equals 23561

OR Home City equals Omaha)

AND Home State/Prov equals NY

Now it will do what the first expectation was.

A good sanity check is to run a query and see that the number of records meets your expectations. For example, if you have 10,000 records in your database, and a query you created returns 10,000 records, something is probably wrong. Ditto if you get 0, but of course expect more.