We have the data tables from Netsuite ELT'd into a DW. I'm trying to build a query that relates TRANSACTIONS or TRANSACTION_LINES to the CUSTOMERS table.
What I've tried:
1.) transaction and transaction_lines does not have a customer_id so there's no direct join available
2.) None of the transaction mapping or link tables contain the customer id
3.) I've been browsing the schema browser https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2016_1/odbc/record/transaction.html, but I simply cannot figure out how to relate these two entities
Does anyone know how to relate transactions to customers?
Please note: This is specifically related to the Netsuite data model and how these two tables relate to each other. I'm not specifically looking for any SQL help, strictly how to associate a transaction with a customer.
The joining field is entity_id on transactions
select tl.item_id, tl.item_count
from transaction_lines tl, transactions t, customers c
where
c.customer_id = t.entity_d and tl.transaction_id = t.transaction_id
Your syntax may vary