Query the recipient table querying-recipient-table
In this example, we want to recover the names and emails of recipients whose email domain is 鈥渙range.co.uk鈥 and who don鈥檛 live in London.
-
Which table should we select?
The recipient table (nms:recipient)
-
Fields to be selected as output columns
Email, Name, City and Account number
-
What are the filtering conditions of the recipients?
city and email domain
-
Is a sort configured?
Yes, based on Account number and Last name
To create this example, apply the following steps:
-
Click Tools > Generic query editor鈥 and choose the Recipients (nms:recipient) table. Then click Next.
-
Choose: Last name, First name, Email, City and Account number. These fields are added to Output columns. Then click Next.
-
Sort the columns to display them in the right order. Here we want to sort account numbers in descending order and names in alphabetical order. Then click Next.
-
In the Data filtering window, refine your search: choose Filtering conditions and click Next.
-
The Target element window lets you enter the filter settings.
Define the following filter condition: recipients with an email domain equal to 鈥渙range.co.uk鈥. To do this, choose Email domain (@email) in the Expression column, choose equal to in the Operator column and enter 鈥渙range.co.uk鈥 in the Value column.
-
If needed, click the Distribution of values button to view a distribution based on the email domain of prospects. A percentage is available for each email domain in the database. Domains other than 鈥渙range.co.uk鈥 are displayed until the filter is applied.
A summary of the query is displayed at the bottom of the window: Email domain equal to 鈥榦range.co.uk鈥.
-
Click the Preview to get an idea of the query result: only 鈥渙range.co.uk鈥 email domains are displayed.
-
We will now change the query to find contacts not living in London.
Select City (location/@city) in the Expression column, different from as an operator and enter London in the Value column.
-
This will take you to the Data formatting window. Check the column order. Move the 鈥淐ity鈥 column up under the 鈥淎ccount number鈥 column.
Uncheck the 鈥淔irst name鈥 column to remove it from the list.
-
In the Data preview window, click Start the preview of the data. This function calculates the result of the query.
The Column results tab shows the query result in columns.
The result shows all recipients with an 鈥渙range.co.uk鈥 email domain who do not live in London. The 鈥淔irst name鈥 column is not shown because it was unchecked during the previous stage. Account numbers are sorted in descending order.
The XML result tab shows the result in XML format.
The Generated SQL queries tab shows the query result in SQL format.
