1

I using a query that selects a check type "checks" and looks in the log file to find the most recent log entry referencing this check. 

SELECT checks.*, logs.last_completed_on, logs.completed_by
    FROM checks INNER JOIN 
         (
             SELECT check_id, MAX(completed_on) AS last_completed_on,
             completed_by FROM checks_log GROUP BY check_id
          ) logs    
ON checks.id = logs.check_id

This query works but i need to create a view for it. This is the first time I've used views so i don't know a lot about them but i read that its not possible with this type of query...

My question is whether there was a way to restructure it in any way?

I guess another solution would be to call this query on a specific check id for every row in a table? It sounds bad practice though... and slow, but i'm not sure.

Thanks

geoff whitehead
  • 67
  • 1
  • 7

2 Answers2

0

A view in MySQL cannot handle subqueries in the from clause. So, you will need somewhat different logic.

This version should work:

SELECT c.*, cl.completed_on as last_completed_on, cl.completed_by
FROM checks c INNER JOIN 
     checks_log cl
     ON c.check_id = cl.check_id
WHERE cl.completed_on = (SELECT MAX(cl2.completed_on)
                         FROM checks_log cl2 
                         WHERE cl2.check_id = cl.check_id
                        );

MySQL does allow subqueries in the SELECT and FROM clauses, so this should be ok.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

As long as the query is working correctly you can create a view from it.
CREATE VIEW {insertnamehere} as (insertyourqueryhere)
Try with and without the parenthesis. I don't know what tool you're using*

FirebladeDan
  • 1,069
  • 6
  • 14