Sample SQL Query to Get Line Items in a Sales Order Transaction

The SQL Sample below shows how to get the line item name of a (Without Tax and Discount) Sales Order Transaction.

This script is equivalent to doing a Transaction Sales Order Saved Search and setting Filter: Main Line = true.

Sample ODBC SQL Code:

select transaction_lines.transaction_id, items.type_name, items.name
from transaction_lines
  join items on transaction_lines.item_id = items.item_id
  join transactions on transaction_lines.transaction_id = transactions.transaction_id
where transactions.transaction_id = '1095'
  and transaction_lines.do_not_display_line='No'
  and items.type_name <> 'Sales Tax Item'
  and items.type_name <> 'Sales Tax Group'
  and items.type_name <> 'Discount'
  and items.type_name <> 'Shipping Cost Item'