Anyone with suiteQL knowledge, When i query "SELECT l.id, l.name, l.mainaddress FROM location l" i get my locations and an "id" for the address, but am unabel to join any tables known to get the country code. I found a "locationMainAddress" table, but can't seem to join it. Any pointers?

build then dataset in the analytics ui first, then export it suiteql

Also 2020.2 has a new Records Catalog which is very useful for figuring out SuiteQL joins

@J. Scot Johnston This should help:

SELECT

Location.Name,

Address.Addressee,

Address.Addr1,

Address.Addr2,

Address.Addr3,

Address.City,

Address.State,

Address.Zip,

Address.Country

FROM

Location

LEFT OUTER JOIN LocationMainAddress AS Address ON

( Address.Nkey = Location.MainAddress )

Thanks for the feedback! 😃 It was extremely helpful

This is not the first and I assume not the last time you've saved the day for me @tdietrich. I am just curious, where do you go to get a full list of all the tables we can query?

Hi @Aaron Lozier! I'm glad I could help. You can get table info from the Records Catalog, and pull everything via its API. I wrote about it here: https://timdietrich.me/blog/netsuite-records-catalog-api/ That's also how I'm displaying the table info in the latest version of the SuiteQL Query Tool: https://timdietrich.me/blog/netsuite-suiteql-query-tool-suitelet-update-20210316/ That all being said, I do wish we had access go metadata tables in SuiteQL itself.

Awesome, thank you. I have really gotten a lot out of your blog. I really appreciate it