RE: SuiteQL Join Address information properly

I am trying to just simply join the full address,city,state zip to the customer in SuiteQL.

Using MartyZigmans NetSuite SQL Query Tool.

I am successfully using Restlets and suiteql but am stymied by the join of address.


select
b.addressbookaddress,
b.label,
b.internalid,
b.entity,
c.id CustomerID,
c.entityid as EntityID,
c.companyName as CompanyName
from customer c
left outer join customeraddressbook b on (b.entity = c.entityid)
order by companyname


the above query works fine.

Of course, the above query does not include the actual address,  but when I try to join the Address table I am not sure why but I get an error.

======

Search error occurred: Record ‘address’ was not found.

======

Below is the query with the join for the address table.

 


select
b.addressbookaddress,
b.label,
b.internalid,
b.entity,
c.entityid as EntityID,
c.companyName as CompanyName
from customer c
left outer join customeraddressbook b on (b.entity = c.entityid)
left outer join address a on (a.id = b.addressbookaddress)
order by companyname

ericj Rookie Asked on June 8, 2021 in SuiteScript.
Add Comment
1 Answers

Hi,

Give this a try:

SELECT
customer.id,
customer.companyname,
customeraddressbook.addressbookaddress,
customeraddressbook.label,
customeraddressbookentityaddress.nkey,
customeraddressbookentityaddress.addr1
FROM customer
LEFT OUTER JOIN customeraddressbook on (customer.id = customeraddressbook.entity)
LEFT OUTER JOIN customeraddressbookentityaddress ON (customeraddressbook.addressbookaddress = customerAddressbookEntityAddress.nkey)

Thanks,

Chris

Intermediate Answered on June 23, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.