10 Replies Latest reply on Apr 12, 2017 5:20 PM by user19752

    How do I make such ExecuteSQL query?



      I'm struggling with a query because of FileMaker ExecuteSQL limitations and I'm hoping someone can help.

      I have two tables Sale & SaleLine that are related by field SaleUUID but table Sale have also OriginalSaleUUID field, which is a parent UUID.

      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.


      Table Sale:



      Table SaleLine:


      11Item A3
      21Item B1
      32Item A1
      43Item A1


      Expected Result:


      Item NameItemQuantity
      ItemQuantity (Return)
      Item A32
      Item B10


      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