Docs Home → MongoDB Manual Show
On this page
➤ Use the Select your language drop-down menu in the upper-right to set the language of the following examples. This operation corresponds to the following SQL statement: The following example selects from the This operation corresponds to the following SQL statement:
The following example retrieves all documents from the NoteAlthough you can express this query using the The operation corresponds to the following SQL statement:
Refer to the Query and Projection Operators document for the complete list of MongoDB query operators. A compound query can specify conditions for more than one field in the collection's documents. Implicitly, a logical The following
example retrieves all documents in the The operation corresponds to the following SQL statement:
See comparison operators for other MongoDB comparison operators. Using the The following example retrieves all documents in the collection where the The operation corresponds to the following SQL statement:
NoteIn the
following example, the compound query document selects all documents in the collection where the The operation corresponds to the following SQL statement:
Note
MongoDB supports regular expressions For additional query examples, see:
For reads to replica sets and replica set shards, read concern allows clients to choose a level of isolation for their reads. For more information, see Read Concern. Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results. To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. If you’re not sure how to make this happen, see Introduction to queries. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results. Here are some examples of commonly used criteria you can use as a starting point to create your criteria. The examples are grouped by data types. In this topic
Introduction to query criteriaA criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Access. The following tables shows some sample criteria and explains how they work.
As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references. This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals. Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this: City = "Chicago" AND BirthDate < DateAdd (" yyyy ", -40, Date())
1. The City and BirthDate fields include criteria. 2. Only records where the value of the City field is Chicago will satisfy this criterion. 3. Only records of those who are at least 40 years old will satisfy this criterion. 4. Only records that meet both criteria will be included in the result. What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them? If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.
1. The City criterion is specified in the Criteria row. 2. The BirthDate criterion is specified in the or row. Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below: City = "Chicago" OR BirthDate < DateAdd (" yyyy ", -40, Date()) If you need to specify more alternatives, use the rows below the or row. Before you continue with the examples, note the following:
Criteria for Text, Memo, and Hyperlink fieldsNote: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named Long Text. The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid.
A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) = "http://www.microsoft.com/", where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.
Criteria for Number, Currency, and AutoNumber fieldsThe following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.
Criteria for Date/Time fieldsThe following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.
Criteria for Yes/No fieldsAs an example, your Customers table has a Yes/No field named Active, used to indicate whether a customer's account is currently active. The following table shows how values entered in the Criteria row for a Yes/No field are evaluated.
Criteria for other fieldsAttachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments. Lookup fields There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created. Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields. The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2. If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:
Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:
See AlsoIntroduction to queries Create a simple select query What type of query creates a new table using the query results?A make table query retrieves data from one or more tables, and then loads the result set into a new table. That new table can reside in the database that you have open, or you can create it in another database.
Which of the following uniquely identifies each row in the table using the values in two or more columns?A primary key is the column or columns that contain values that uniquely identify each row in a table.
Which of the following is the column or collection of columns that uniquely identifies a given row in that table?A specific choice of columns which uniquely identified rows called primary key.
Which operator is used to concatenate every row in the first table with every row in the second table?The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.
|