This section discusses some of the features and restrictions that are specific to how the cmdlet supports joins. If possible the CData Cmdlets PowerShell Module for NetSuite will attempt to perform joins directly in NetSuite. If NetSuite cannot handle the join, it will instead be performed client side when SupportEnhancedSQL is set to true.

The NetSuite supports join queries on tables that have a relationship defined in NetSuite. Only left outer joins are supported directly by NetSuite.

When you use the cmdlet to join tables with SQL, this is the same as creating a Saved Search in the NetSuite UI and then selecting the option to get back data from other related tables.

The following query retrieves all Notes associated with each Customer:

SELECT n.Note AS MyNote, c.InternalId AS CustomerId 
FROM Customer c 
LEFT OUTER JOIN Note n
ON n.Entity_InternalId = c.InternalId

Joins of more than two tables are supported. The following query retrieves Customer and BillingSchedule information about each SalesOrder:

SELECT Customer.AccountNumber AS CustomerAcctNumber, BillingSchedule.InitialAmount AS InitialAmount, SalesOrder.InternalId AS SalesOrderId
FROM SalesOrder 
LEFT OUTER JOIN Customer ON SalesOrder.Entity_InternalId = Customer.InternalId
LEFT OUTER JOIN BillingSchedule ON SalesOrder.BillingSchedule_InternalId=BillingSchedule.InternalId

Due to limitations in the NetSuite API, joins are not supported for the following tables:

• Child tables
• Custom record tables
• Tables listed in the "Simple Tables" section in Tables