I'm struggling with a query because of FileMaker ExecuteSQL limitations and I'm hoping someone can help.
I have two tables
SaleLine that are related by field
SaleUUID but table Sale have also
OriginalSaleUUID field, which is a parent
What I am trying to achieve is to display a list of items for particular sale and their quantities together with returned quantities (it could be 0 returned, this mean no records in Sale & SaleLine tables for return or multiple returns for one item and a sale).
I hope this make sense.
|Item Name||ItemQuantity||ItemQuantity (Return)|
If this would be MySQL, I would do this way... but because it's not, how can do it? Thanks.
SELECT what_I_need, SUM(IF(OriginalSaleUUID IS NULL, SaleLineQuantity, 0)) quantity, SUM(IF(OriginalSaleUUID IS NOT NULL, SaleLineQuantity, 0)) ret FROM SaleLine
JOIN Sale USING(SaleUUID)
GROUP BY IFNULL(OriginalSaleUUID, SaleUUID), SaleLineItemName