Custom SQL Guide

Basic Formatting Rules

1. Must be a SELECT Query - goal is to select the record IDs of the people you want to be on the list. You must SELECT the ID field of a Contact, Target, or Lead.
2. JOIN statements are allowed after the FROM statement and before the WHERE statement (ex. you want to join the contacts_cstm table to get to custom fields)
3. The WHERE statement must be the last thing in your query. ORDER BY, LIMIT, HAVING, GROUP BY are not allowed.
   Do not add ‘deleted’ checks to your sql query. We will make sure only non-deleted Contacts/Targets/Leads are added to your lists.

MySQL Syntax Reference

Unallowed Keywords

You cannot use the following SQL Keywords in your queries:

• LIMIT
• HAVING
• GROUP BY (we will make sure no duplicates are added)
• ORDER BY
• DISTINCT (Mailchimp uses the email as a record's unique identifier and will prevent duplicates)

Example Queries:

• Only targets whose last name is Smith and title is CEO or COO

SELECT prospects.id 
FROM prospects 
WHERE prospects.last_name = ‘Smith’ AND (prospects.title = ‘CEO’ OR prospects.title = ‘COO’)

• Only leads where age is less than 8 (custom field)

SELECT leads.id 
FROM leads 
INNER JOIN leads_cstm ON leads.id=leads_cstm.id_c 
WHERE leads_cstm.age_c < 8

• Only contacts where Account Industry is Oil and Gas (related fields)

SELECT contacts.id
FROM contacts
INNER JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id
INNER JOIN accounts ON accounts_contacts.account_id=accounts.id
WHERE accounts.industry = ‘Oil and Gas’

For further assistance, contact [e-mail].