When working with customer data, having access to accurate and well-structured address information is crucial for reporting, integrations, and business operations. While standard saved searches can help, SuiteQL provides more advanced querying capabilities, allowing you to join tables and retrieve data that isn't easily accessible through the UI alone.
Below is a sample query that retrieves each customer’s ID, name, and associated address components using a join between the customer and entityaddress tables.
Sample SuiteQL Query:
SELECT
c.id AS customer_id,
c.entityid AS customer_name,
a.addr1 AS address_line_1,
a.addr2 AS address_line_2,
a.city,
a.state,
a.zip,
a.country,
a.addressee
FROM
customer c,
entityaddress a
WHERE
c.id = a.recordowner
Query Breakdown
SELECT clause
This defines the columns (fields) you want the query to return:
- c.id AS customer_id: Retrieves the internal ID of the customer.
- c.entityid AS customer_name: Retrieves the customer’s name or unique identifier.
- a.addr1 through a.country: Retrieves address components from the entityaddress table, such as street, city, state, and country.
- a.addressee: Retrieves the name or recipient listed on the address.
FROM clause
Specifies the tables you're querying:
- customer c: The customer table (aliased as c).
- entityaddress a: The address table (aliased as a), which stores address records for various entity types (customers, vendors, etc.).
WHERE clause
This condition connects the two tables:
- a.recordowner is the internal ID of the entity (in this case, a customer) that owns the address.
- By matching c.id = a.recordowner, you ensure you're retrieving only the addresses that belong to each specific customer.
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
We’d love to hear how you integrate SuiteQL into your NetSuite processes! Share your insights and experiences in the NetSuite Admin Corner.