im trying to create a query that returns to me all items sold as inventory items and not as a kit, I want to check if a certain item has any related records on the item level, but may have sold on the kit

Hi Sim, The below query will return all item lines from Sales Orders and Cash Sales where the item is usually part of a kit item but has been sold individually (where the parent item isn't the kit is normally part of). It will return the inventory item ID and the internal ID to its (normally) related kit. Is this what you are looking for? SELECT t.id AS transaction_id, t.type AS transaction_type, t.trandate AS transaction_date, i.id AS item_id, i.itemid AS item_name, kit.id AS related_kit_id, kit.itemid AS related_kit_name, tl.quantity FROM transaction t INNER JOIN transactionline tl ON tl.transaction = t.id INNER JOIN item i ON i.id = tl.item INNER JOIN itemmember im ON im.item = i.id INNER JOIN item kit ON kit.id = im.parentitem AND kit.itemtype = 'Kit' WHERE t.type IN ('SalesOrd', 'CashSale') AND tl.mainline = 'F' AND tl.taxline = 'F' AND i.itemtype = 'InvtPart' AND i.itemtype <> 'Kit' AND NOT EXISTS ( SELECT 1 FROM transactionline tl2 INNER JOIN item ki ON ki.id = tl2.item WHERE tl2.transaction = t.id AND ki.id = kit.id AND tl2.mainline = 'F') Is there a reason you are including invoices? Do you create adhoc invoices unrelated to any sales order or cash sales that you want to make sure are included in the results or something? If so, you probably want to make sure you ignore any invoices where there is a value in the createdfrom field. Otherwise you could return duplicate item line results which might also be contributing to the false positives. Or maybe only look at invoices and cash sales depending on the outcome you need? Hope this helps! 🙂 Hannah