OQL Inner Join causes Empty Report

I am a relative newbie working on a prototype for a client. I have tried reading everything I can to correct the problem I am having and have run out of iterations to try. I have written SQL in the past but this one has me stumped. The overall solution: A large list of orders end up in an entity ‘Transaction’. They all contain a Product ID which will match objects in an entity ‘Product’. I have created a report with 2 columns, product name and the total sales for each product.   The details below create an empty report. If I remove the inner join and just report by ProdID, it works. As soon as I put in the inner join, I get an empty report. I am working in Studio Pro 8.15.1. Any guidance would be greatly appreciated – I’m sure it is something small that I am just not seeing…
1 answers

What if you use left join instead of inner join? Hm, the same result probably.

Also, investigate the data in your database, does every transaction having ProdID xyz, also have an object in association table Transaction_Product? You can check this by comparing the Mendix-id’s of both objects: transaction/id and product/id.

If that still does not clear up the issue, add a snapshot of three records in Transaction, the related records in transaction_Product, and the one record in Product to this forum-question. That way we can see the data you are trying to sum.

On https://mydemoversion8-sandbox.mxapps.io/p/OQL, your query is similar to the button “Count, Sum and Group by enumeration”

select c.CustomerType, count(*) Ordercount, sum(o.TotalPrice) TotalSpent
from "Sales.Customer" c
join "c/Sales.Order_Customer/Sales.Order" o
group by c.CustomerType;

Just replace their ‘Customer’ with your ‘Product’ and their ‘Order’ with your ‘Transaction’