--- schema_version: "1" id: cfbe71f7d98f082b2917aae978765f2976345fc1cf948f5e0ae5c51d8535c586 title: "Query: Subscriber Status" collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/mktg/264-0-0/mc_as_query_find_subscriber_status_ref.md canonical_url: https://help.salesforce.com/s/articleView?id=mktg.mc_as_query_find_subscriber_status_ref.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: da50399f9b468b071b2e99248fcb3387167894a9f59c849abe39d1c9f90c5bdb description: Find subscribers on a list and their statuses. Or, find only subscribers with a specific status, such as active or unsubscribed. products: - Marketing - Marketing Cloud Engagement release: "264" --- # Query: Subscriber Status Find subscribers on a list and their statuses. Or, find only subscribers with a specific status, such as active or unsubscribed. ## Before You Begin To use this example, you need a working knowledge of SQL. You can copy and paste the code samples, but you must configure them for your setup. ## Create a Target Data Extension In Contact Builder or Email Studio, create a standard data extension with these properties and fields. This data extension is the destination for your query's data output. If the source data extension includes duplicate entries, deselect **Primary Key** for the SubscriberKey value.
Target Data ExtensionDataExtensionWithStatus
External KeyDataExtensionWithStatus
![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-mktg-mc_journeys_and_automations-264-0-0-production-enus/e08cdd71-c1c1-4ae5-a5e9-8a6dad583ad4/images/icon_note.png) Note If you create the data extension in Email Studio, the Nullable column replaces the Required column. If a field isn’t required, select **Nullable**. If a field is required, don’t select Nullable. | Primary Key | Name | Data Type | Required | Length | Default Value | | --- | --- | --- | --- | --- | --- | | X | SubscriberKey | Text | X | 254 |   | |   | Status | Text | X | 25 |   | | X | ListName | Text | X | 100 |   | ## Create a Query Activity In Automation Studio, create an SQL query with these properties.
Query NameDataExtensionWithStatus
External KeyDataExtensionWithStatus
## Sample Query Copy and paste this query. ``` SELECT l.listname, de.subscriberkey, l.[status] FROM [MY_DE_NAME] AS de INNER JOIN [_listsubscribers] AS l ON de.subscriberkey = l.subscriberkey WHERE l.listname = MY_LIST_NAME ``` ## Finalize the Query Activity Customize the query. Use the line numbers in Automation Studio to identify which lines to modify. - On line 4, replace `MY_DE_NAME` with the name of the source data extension. - On the last line, replace `MY_LIST_NAME` with the name of your subscriber list. - If the source data extension doesn’t include a SubscriberKey field, replace `de.subscriberkey` on lines 2 and 6 with `de.yourfield`. For example, `de.emailaddress` or `de.customerid`. Link the query to the target data extension.
Target Data ExtensionDataExtensionWithStatus
Data ActionOverwrite
Save your query activity, and then create and activate an automation that uses the activity. ## Query Variation: Return Other Fields To return other fields from the source data extension, add those fields to the target data extension and the query. For example, if your source data extension includes a PolicyNumber field, add a field for PolicyNumber to the target data extension. Then in the query, insert `de.policynumber` in the select statement. ``` SELECT l.listname, de.subscriberkey, de.policynumber, l.[status] FROM [MY_DE_NAME] AS de INNER JOIN [_listsubscribers] AS l ON de.subscriberkey = l.subscriberkey WHERE l.listname = MY_LIST_NAME ``` ## Query Variation: Return Subscribers with a Specific Status To return results for subscribers with a specific status, such as active, add this line to the end of the query. You can also replace `active` with another status, such as `bounced`, `held`, or `unsubscribed`. ``` AND l.[status] = active ``` #### See Also - [Create a Data Extension in Contact Builder](https://help.salesforce.com/s/articleView?id=mktg.mc_cab_create_a_new_data_extension.htm&type=5&language=en_US) - [Create a Data Extension in Email Studio (Enhanced)](https://help.salesforce.com/s/articleView?id=mktg.mc_es_create_data_extension.htm&type=5&language=en_US) - [Build a SQL Query Activity](https://help.salesforce.com/s/articleView?id=mktg.mc_as_use_the_sql_query_activity.htm&release=264.0.0&type=5 "In Automation Studio, build a SQL query activity that retrieves the data that you’re interested in, and then add it to an automation.") - [Optimizing a SQL Query Activity](https://help.salesforce.com/s/articleView?id=mktg.mc_as_optimize_the_query_activity.htm&release=264.0.0&type=5 "SQL query activities time out after 30 minutes. You can prevent timeouts and failures by optimizing your SQL query activities. If your query consistently runs longer than 10 minutes, we recommend using a different tool, such as Salesforce Customer Data Platform, to transform your data.") - [SQL Reference](https://help.salesforce.com/s/articleView?id=mktg.mc_as_sql_reference.htm&release=264.0.0&type=5 "Get SQL query information for Automation Studio’s SQL Query activity at a glance.") - [Subscribers](https://help.salesforce.com/s/articleView?id=mktg.mc_es_subscribers.htm&type=5&language=en_US)