NetSuite query - select transactions with messages rows

Question

I want to select Transaction with/without associated messages rows.

The purpose is to define is Email sent from transaction. That means I need to fetch all transactions regardless of whether they have related messages. enter image description here

Answer 1

I'm assuming you wanted to do this via SuiteQL Queries. If so, here's how:

To get Transactions with Messages:

SELECT M.transaction.*, T.*
FROM Message as M, transaction as T
WHERE M.transaction IS NOT NULL 
AND M.transaction = T.id

To get Transactions without Messages:

SELECT T.*
FROM transaction as T
WHERE T.id NOT IN (
SELECT M.transaction 
FROM Message as M
WHERE M.transaction IS NOT NULL 
) M.transaction IS NOT NULL