skcah
Writing / Netsuite

Why SuiteQL Customer Address Joins Return Duplicate Rows

Diagnose duplicate customer rows in SuiteQL address queries and choose the correct result grain instead of relying on DISTINCT.

Ryan Sheehy2 minute read

A customer-to-address join is normally one-to-many. If a customer has four address-book entries, a query at address level should return four rows for that customer.

Those rows are not duplicates merely because the customer columns repeat.

Decide the result grain first

Choose what one row represents:

  • one customer;
  • one customer address-book entry;
  • one customer and its two default addresses;
  • one transaction joined to one customer address.

The correct query follows from that choice.

One row per address-book entry

Use the address-book ID as part of the result:

SELECT
  customer.id AS customer_id,
  customer.entityid,
  customeraddressbook.id AS address_book_id,
  customeraddressbook.label,
  customeraddressbookentityaddress.addr1,
  customeraddressbookentityaddress.city
FROM customer
JOIN customeraddressbook
  ON customeraddressbook.entity = customer.id
JOIN customeraddressbookentityaddress
  ON customeraddressbookentityaddress.nkey =
     customeraddressbook.addressbookaddress

Repeated customer IDs are expected. Each address_book_id identifies a different address entry.

One row per customer

If only one address is required, filter the relationship before selecting it:

LEFT JOIN customeraddressbook
  ON customeraddressbook.entity = customer.id
 AND customeraddressbook.defaultshipping = 'T'

This is clearer than retrieving every address and asking DISTINCT to hide the extra rows.

Find the join that multiplies rows

Add joins one at a time and count stable identifiers:

SELECT
  customer.id,
  COUNT(*) AS joined_rows,
  COUNT(DISTINCT customeraddressbook.id) AS address_rows
FROM customer
LEFT JOIN customeraddressbook
  ON customeraddressbook.entity = customer.id
GROUP BY customer.id
HAVING COUNT(*) > 1

If another one-to-many table—such as transactions, contacts or notes—is joined to the same customer, the result can multiply. Four addresses joined to three transactions can produce twelve combinations.

Aggregate or filter each one-to-many branch before joining them together.

When DISTINCT helps

DISTINCT is useful when every selected column is genuinely repeated because of the join path. It is not a substitute for deciding which address should win.

If two rows differ by address-book ID, label or default flag, DISTINCT correctly keeps both.

Troubleshooting checklist

  1. State what one output row represents.
  2. Include internal IDs while debugging.
  3. Count rows after every added join.
  4. Filter the default address in the join where appropriate.
  5. Avoid joining two unrestricted one-to-many relationships.
  6. Use aggregation only when its selection rule is explicit.
  7. Add DISTINCT last, after understanding the multiplication.