MariaDB select from inner joined query-CodePudding

I am not able to further select from joined subquery.

I have data in three tables: "events", "records" and "work_list" (names not important). Each table has one piece of the puzzle where work_list is the shortest and contains top-level data, and the events table tracks many tiny frequent events.

I need to calculate many statistical variables from the events based on some key variables defined in work_list like weighted moving average etc. Nevertheless, I have those metrics ready and working, but I have problems filtering the data in events based on selected parameters stored in work_list.

Here is a piece of code that does not work. The SELECT * is not important, I will change it to be more meaningful later, it is like so because of clarity. However, I have tried many selections in place of the * without success.

So what is wrong with this query from subquery?

Query example 1:

SELECT * FROM
    (SELECT events.id, events.type,events.timestamp, work_list.task
    FROM 
        ( events
            INNER JOIN records ON events.record_id = records.id
            INNER JOIN work_list ON records.work_list_id = work_list.id
        )
    WHERE work_list.customer_number = '1234' AS subquery
);

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as subquery ) LIMIT 0, 25' at line 8

I check that the inner joined subquery works well and it returns a normal looking table

Query example 2:

SELECT events.id, events.type,events.timestamp, work_list.task
FROM
    ( events
    INNER JOIN records ON events.record_id = records.id
    INNER JOIN work_list ON records.work_list_id = work_list.id
    )
WHERE work_list.customer_number = '1234';

I have tried using parenthesis in different orders, and I have changed selected variables in here SELECT events.id, events.type,events.timestamp, work_list.task. I have also tried to find this kind of example on the internet without success. It makes me wonder if this is actually a poor way of doing this kind of query. However, I have the calculation part ready and I would really like to get this subquery to play along. So even if there might be better structures for this problem, I am mostly interested in solutions that maintain this structure.

The goal of this phase is to filter the events table for further queries that are coded on top of it replacing the "SELECT *".

Jussi

Edit: These are the final calculations made earlier which I plan to use when I figure out the problem with Query example 1.

Query example 3:

SELECT *, ((SUM(rate * diff) OVER(ORDER BY startTime
     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)) /
     (SUM(diff) OVER(ORDER BY startTime
     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW))) as rate_WMA
FROM (
    SELECT id, startTime, counts, diff, (counts / diff)*3600 as rate FROM (
        SELECT id, TIMESTAMPDIFF(SECOND, MIN(timestamp), MAX(timestamp))AS diff, SUM(change) as counts, MIN(timestamp) as startTime
        FROM `the filered subquery here`
    
        GROUP BY id 
    )AS subquery WHERE diff > 0 
) as totaltotal;

CodePudding user response：

You have extra parenthesis (no need for those) and the alias for the subquery should be placed after the subquery:

SELECT * 
FROM (
  SELECT events.id, events.type,events.timestamp, work_list.task
  FROM events
    INNER JOIN records ON events.record_id = records.id
    INNER JOIN work_list ON records.work_list_id = work_list.id
  WHERE work_list.customer_number = '1234'
) AS subquery;