We are trying to make a single SuiteQL query (through the REST API) to get multiple Customer records with their relevant Contacts and Addresses. If we made this query in the REST API directly, we would have to make an individual request to get every Customer record - which would be a very slow way to sync potentially thousands of records!
We have figured out - more-or-less by guessing - the table names to get Contacts with the customers with a JOIN like this:
SELECT c.id,c.companyName,c.phone,cn.firstName,cn.lastName,cn.title FROM customer as c JOIN contact as cn ON c.id = cn.company ORDER BY c.id ASC
Is there an Address table that would let us similarly query all the customers' addresses?
Similarly, we are trying to query UoMs - we can do
SELECT id,name FROM unitstype
but what table name do we need to use to get the actual UoMs that go in the UoM group?
Where can we find documentation about the schema?
The REST API documentation (eg. https://system.netsuite.com/help/helpcenter/en_US/APIs/REST_API_Browser/record/v1/2019.2/index.html#/definitions/custome… ) doesn't actually apply to SuiteQL. We found this schema doc: https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2016_1/odbc/record/address_book.html but it doesn't seem to be correct for SuiteQL.
Is there some documentation we're missing? Our progress by guesswork is very slow.