In New to NetSuite | SuiteQL Overview and New to NetSuite | Understanding SuiteQL Syntax articles, we explored the fundamental concepts, capabilities, and structure of SuiteQL.
One of SuiteQL’s most powerful capabilities is its ability to join records, allowing you to access and combine related data from multiple record types within a single query. Instead of running separate searches for each record type — for example, one for Customers and another for Transactions — you can gather all relevant information in one efficient result set.
Joined records in SuiteQL are especially useful when your data is distributed across different but related records. By defining the relationships between these records, you can retrieve fields from two or more connected record types, such as displaying a Customer’s contact details directly alongside their Sales Orders, Invoices, or Payments.
SuiteQL Syntax: Joined Records
Basic Join Syntax
SELECT a.field1, b.field2
FROM tableA a
JOIN tableB b
ON a.link_field = b.link_field
WHERE [optional conditions]
Key components:
- JOIN (or INNER JOIN): Combines rows from two tables based on a related column.
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table (if any).
Example 1: Basic Join – Transactions with Customer Name
SELECT t.tranid, c.entityid AS customer_name, c.email
FROM transaction t
JOIN customer c
ON t.entity = c.id
WHERE t.type = 'SalesOrd'
ORDER BY t.tranid
- Lists sales orders (“SalesOrd"), showing both order details and related customer info.
Example 2: Join with Transaction Lines (Child Record)
SELECT t.tranid, tl.item, tl.quantity, tl.foreignamount
FROM transaction t
JOIN transactionLine tl
ON t.id = tl.transaction
WHERE t.type = 'SalesOrd'
- Shows each sales order along with the individual line items and quantities.
Example 3: Nested Joins
SELECT t.tranid, c.entityid AS customer_name, tl.item, tl.quantity
FROM transaction t
JOIN customer c ON t.entity = c.id
JOIN transactionline tl ON t.id = tl.transaction
WHERE t.type = 'SalesOrd'
ORDER BY t.tranid
- Returns each sales order with customer name and related line items.
Common Syntax Points
- Use aliases (t, c, tl) for readability, especially in queries with multiple joins.
- The linking fields (entity, id, transaction) may be different depending on your records and relationships. Always confirm field names using the NetSuite Records Browser or Schema Browser.
- Multiple joins are supported, including LEFT JOIN, RIGHT JOIN, and INNER JOIN.
To know more about SuiteQL Join Types, check these New to NetSuite articles:
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.
Stay tuned for upcoming articles on how to use SuiteQL Syntax in SuiteCloud Product Area. Stay updated by following the New to NetSuite > SuiteCloud category to receive notifications whenever new articles are published.
