ODBC > Link GIFT_CERTIFICATE table to TRANSACTION_LINES table

On the Help Guide, there is currently no foreign key or field that directly links the GIFT_CERTIFICATE table and TRANSACTION_LINES table.
 
Users can link the GIFT_CERTIFICATE table to TRANSACTION_LINES table in order to identify the Gift Certificates used on Transactions and how much is left for existing Gift Certificates. This is done by comparing the Memo field on the Transaction Lines table to the Gift Certificate ID on the Gift Certificate table. Users could do this by using the following SQL statements:
 
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'
 
These SQL statements will show the AMOUNT of Gift Certificates used per Transactions, as well as the AMOUNT_AVAILABLE and AMOUNT_AVAILABLE_BILLED for Gift Certificates.