SuiteQL: Get Customer Address Details

This is the hub note for listing NetSuite customers with their address-book rows in SuiteQL. Start here for the full join; use the related guides for defaults only, duplicate-row diagnosis, and table responsibilities.

If you’re using SuiteQL to obtain a listing of customers and their addresses, the following query is a solid starting point:

SELECT DISTINCT
	C.id AS customer_id,
	C.entityid AS customer_number,
	CAB.id AS address_book_id,
	CAB.label AS address_label,
	CAEA.addr1 AS addr_line1,
	CAEA.addr2 AS addr_line2,
	CAEA.addr3 AS addr_line3,
	CAEA.state AS state,
	CAEA.city AS city,
	CAEA.zip AS zip,
    CAEA.country AS country,
	CAB.defaultbilling AS default_billing,
	CAB.defaultshipping AS default_shipping,
	CAB.isresidential AS is_residential
FROM
	Customer C
	LEFT JOIN CustomerAddressBook CAB
		ON C.id = CAB.entity
	LEFT JOIN CustomerAddressBookEntityAddress CAEA
		ON CAB.addressbookaddress = CAEA.nkey
WHERE
-- ... any conditions on Customer record, i.e. Status is Active, etc

This query contains the following details:

The DISTINCT clause removes any duplication of Address entries in the Customer’s record.

CAB.defaultbilling and CAB.defaultshipping identify the address-book entries selected as the customer’s defaults. Keep those flags in the result even when you initially need every address; they make it easy to select one preferred address later without changing the joins.

To return only the default billing address, add:

AND CAB.defaultbilling = 'T'

For only the default shipping address, use:

AND CAB.defaultshipping = 'T'

Place those predicates in the join when you still want customers without a matching default address to remain in the result:

LEFT JOIN CustomerAddressBook CAB
    ON C.id = CAB.entity
    AND CAB.defaultshipping = 'T'

This preserves the outer-join behaviour. Putting the same condition in WHERE removes customers for whom no matching address row exists.

The address-book ID is useful when the same physical address appears more than once or labels have been reused. Prefer stable internal IDs over address text when matching rows between executions.

The next section of the query retrieves all the necessary fields that may be needed in your result to fetch the Customer’s address details.

Note that Netsuite uses multiple tables to store the information and a couple of joins are needed between the main Customer record to get to the relevant address data such as street, city, zip and country.

Related SuiteQL guides