New Help to implement query in aggregate

New Help to implement query in aggregate

  

How to implement the following SQL in an aggregate?

SELECT a.id, b.name, COUNT(x.eventid)
FROM tableA a
JOIN tableB b ON a.b_id = b.id
LEFT OUTER JOIN (
SELECT c.b_id, d.eventid
FROM tableC c
JOIN tableD d ON c.c_id = d.c_id
WHERE d.eventname = 'eventname'
) x
ON a.b_id = x.b_id AND x.eventid > a.lastevent
GROUP BY a.id, b.name
ORDER BY a.id

It is using a subquery, another way to get the same result is to use nested JOINS.

Can this be done in an aggregate or I need to use AdvancedQuery?


Thanks,

Stanley

Use an Advanced Query instead.

Thanks.

Solution

Hi,

While I also would use an Advanced query (I'm old school), I think it is possible to do it on an aggregate without too much trouble.

Lets first change the query to use only JOINS:

SELECT 
    a.id, 
    b.name, 
    COUNT (CASE WHEN d.id IS NULL THEN 0 WHEN d.eventid > a.lastevent THEN 1 ELSE 0 END)
FROM tableA a
    INNER JOIN tableB b ON a.b_id = b.id
    LEFT JOIN tableC c
        INNER JOIN tableD d ON d.c_id = c.c_id AND d.eventname = 'eventname'
    ON c.b_id = a.b_id
GROUP BY a.id, b.name
ORDER BY a.id

The only problem here is the "nested" join because you have mixed joins (left with inner)

The way to solve this in an aggregate, that have all the joins at the same level, is to chose wisely the order of the joins in the "source" tab of the aggregate.

So, the nested INNER JOIN must be the first condition at the TOP, to be evaluated first and not removing the line of the results if there is no match between the table c and d, than the first query INNER JOIN is the second join in the aggregate source and the LEFT JOIN is the third JOIN in the source.

Cheers,
Eduardo Jauch

P.S. As a side note, this query seems to need the INNER join between A and B last, as with the LEFT JOIN as the last (as it is), you can have lines without B, if there is a match between A and C.

EDIT: I had to change the SQL in order to take into account the condition table D have with table A. You have to implement this condition in the aggregate using computed field.

Solution

Hi Eduardo,

Thanks for the tip. I will try your suggestion later.

Stanley

Hello Stanley,

I took the liberty of doing a small test.
I may be wrong, as I don't know the data and the logical relationships between the tables.
But...

But with my test data, using an Advanced query with your query and the Aggregate (that was not that complicated at all, in the end), the results were the same.

Here is my Aggregate:

It seems that the nested joins (or subqueries, if they can be changed into joins), in this case, are not really the challenge.

The challenge is the condition in the subquery, that compares D.EventId with A.LastEvent.
Because when doing a JOIN I can't use any other table than the two tables being joined, I'm not able, at the JOIN level, of get rid of the C/D pairs where the D.EventId is lower or equal to the A.LastEvent. 

In order to not count those lines when counting the "D.EventId's", I have to do a small trick. I create a computed field and use the following expression on it:

If(TableD.Id <> NullIdentifier(), If(TableD.EventNumber > A.LastEvent, 1, 0), 0)

Than, instead of "counting", I SUM the values in the column. This way, in the end, I'll be able to eliminate those lines in the counting.

But as I said before, I also think when you spend more than 5 minutes to create an Aggregate, it's time to go SQL... ;)

But I think your query is probably possible, using the approach I used here.

Cheers,
Eduardo Jauch