Joining Tables in Connect
In SuiteAnalytics Connect, you can join two tables in your query to obtain a customized result set. The following example shows how you can joint two Connect tables:
Linking Gift Certificates to Transaction Line Items
You can return Transaction Lines table data together with Gift Certificates table data by querying for records where the Transaction Lines memo
column value matches the Gift Certificates gift_certificate_id
value. In addition, you can return Items table data by querying for records where the Transaction Lines item_id
column value matches the Items item_id
column value, and the value for the Items type_name
column is 'Gift Certificate
'.
Use queries like the following for these data joins:
select * from TRANSACTION_LINES tl, GIFT_CERTIFICATES gc
where tl.MEMO = gc.GIFT_CERTIFICATE_ID select * from TRANSACTION_LINES tl, GIFT_CERTIFICATES gc, ITEMS i
where tl.MEMO = gc.GIFT_CERTIFICATE_ID and tl.ITEM_ID = i.ITEM_ID and i.TYPE_NAME = 'Gift Certificate'