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

    How do I make such ExecuteSQL query?

    sebastijan

      Hey.

      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:

      SaleUUIDOriginalSaleUUID
      1
      21
      31

       

      Table SaleLine:

       

      SaleLineUUIDSaleUUIDSaleLineItemNameSaleLineQuantity
      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