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
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
