sebastijan

How do I make such ExecuteSQL query?

Discussion created by sebastijan on Apr 9, 2017
Latest reply on Apr 12, 2017 by user19752

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

Outcomes