how do I perform an outer join in N/query

AI generated answer (using internal Slack threads):

In N/query, you can perform an outer join by specifying LEFT or RIGHT OUTER JOIN in your SQL statement. Here's an example:

sql
SELECT TransactionLine.Transaction as tid
FROM TransactionLine
LEFT OUTER JOIN Item ON (TransactionLine.item=item.id) 
WHERE item.id=134543

In this example, a LEFT OUTER JOIN is used to return all the records from the TransactionLine table and the matched records from the Item table. If there is no match, the result is NULL on the right side. As for whether to use n/query or n/search, it depends on your use case. If you're dealing with multi-joins and you're comfortable with SQL, n/query can be a faster and more efficient choice. However, for simple lookups or if you want your code to be easily understood by other NetSuite developers who might not be familiar with SQL, n/search could be a better option. For parent-child relationships in custom records, you can use the

joinTo

method for the first join and then use

autoJoin

for subsequent joins. Here's an example:

javascript
var queryMain = query.create({
    type: 'customrecord_sometype'
});
queryMain.condition = queryMain.createCondition({
    fieldId: 'id',
    operator: query.Operator.ANY_OF,
    values: idsToProcess
});
var itemJoin = queryMain.joinTo({
    fieldId: 'custrecord_fieldwithitemrecord',
    target: 'item'
});
var unitsTypeJoin = itemJoin.autoJoin({
    fieldId: 'unitstype'
});
var unitsJoin = unitsTypeJoin.autoJoin({
    fieldId: 'uom'
});

In this example,

joinTo

is used to join the main query to the 'item' record, and then

autoJoin

is used to join the 'item' record to the 'unitstype' and 'uom' records. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******