Limit a left join to latest date in View

Question

I've got a query that's connected to a table which I only want to see the last entered result based on the dtm

This is my query

CREATE OR REPLACE VIEW vwc_students AS 
SELECT u_students.stud_id,u_students.stud_firstname, u_students.stud_middlename,u_students.stud_lastname, CONCAT_WS(' ',u_userse.user_firstname,u_userse.user_lastname) AS stud_editeduser, u_students.stud_edited, u_tbleeditlog.editedDtm
FROM u_students
JOIN u_studsites ON u_studsites.uss_studid = u_students.stud_id
JOIN u_locations ON u_locations.loc_id = u_students.stud_base
JOIN u_users ON u_users.user_id = u_students.stud_loggedid
LEFT JOIN (SELECT editedID, editedDtm FROM u_tbleeditlog WHERE editSite = 1 AND editType = 1 ORDER BY editedDtm DESC LIMIT 1) u_tbleeditlog ON u_tbleeditlog.editedID = u_students.stud_id
LEFT JOIN u_users AS u_userse ON u_userse.user_id = u_tbleeditlog.editedID
WHERE u_studsites.uss_siteid = '1'

but I'm getting the error #1349 - View's SELECT contains a subquery in the FROM clause

I got the code examples from Limiting a left join to returning one result? and MySQL JOIN Multiple Tables With LIMIT Last Table By DateTime per result

Can anyone please tell me if what I'm trying to do is possible, and if so how? :)

UPDATE

Ok I now have the following 2 views as suggested

CREATE OR REPLACE VIEW vwc_students AS 
SELECT u_students.stud_id,u_students.stud_firstname, u_students.stud_middlename,u_students.stud_lastname, u_userse.editedEditor AS stud_editeduser, u_userse.editedDtm AS stud_edited
FROM u_students
JOIN u_studsites ON u_studsites.uss_studid = u_students.stud_id
JOIN u_locations ON u_locations.loc_id = u_students.stud_base
JOIN u_users ON u_users.user_id = u_students.stud_loggedid
LEFT JOIN vwu_editlog AS u_userse ON u_userse.editTypeID = u_students.stud_id AND u_userse.editType = 1
WHERE u_studsites.uss_siteid = '1'

and

CREATE OR REPLACE VIEW vwu_editlog AS 
SELECT u_tbleeditlog.*, CONCAT_WS(' ',u_users.user_firstname,u_users.user_lastname) AS editedEditor
FROM u_tbleeditlog
JOIN u_users ON u_users.user_id = u_tbleeditlog.editedID
ORDER BY u_tbleeditlog.editedDtm DESC LIMIT 1

But this last view is limiting the entire thing to 1 result! How would I limit each unique ID to 1? I also get the message Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available., even though in the original table it is there

Add an `ORDER BY` clause for the date, and then limit your result set to the first row. — jpaugh, Aug 27 '14 at 23:57

score 1 · Accepted Answer · edited May 23 '17 at 12:10

1

Duplicate of: View's SELECT contains a subquery in the FROM clause

As per documentation:

MySQL Docs

The SELECT statement cannot contain a subquery in the FROM clause.

Your workaround would be to create a view for each of your subqueries.

Then access those views from within your view view_credit_status

So maybe you'd first do something like this:

CREATE OR REPLACE VIEW u_tbleeditlog AS 
 SELECT editedID, editedDtm
  FROM u_tbleeditlog
  WHERE editSite = 1
  AND editType = 1
  ORDER BY editedDtm DESC
  LIMIT 1;

edited May 23 '17 at 12:10

Community

1
1

answered Aug 27 '14 at 23:57

Joe T

2,291
1
17
28

Thanks. Sorry I didn't see that one :) – dpDesignz Aug 28 '14 at 00:00
I've just realised this query only returns one result in my view now!? – dpDesignz Aug 28 '14 at 01:15
you're doing a join on a single result, probably need to ditch the LIMIT 1 and use DISTINCT instead. – Joe T Aug 28 '14 at 14:55

score 1 · Answer 2 · answered Aug 27 '14 at 23:58

You are missing the fact that views in MySQL do not allow subqueries in the from clause. They are allowed in the select and where and having clauses, however.

The documentation:http://dev.mysql.com/doc/refman/5.0/en/create-view.html is quite clear:

Subqueries cannot be used in the FROM clause of a view.

In your case, you can probably rewrite the from clause as a correlated subquery in the select clause. You can also use multiple layers of views to do what you want.

Create a separate view for each of the FROM clause.

Limit a left join to latest date in View

2 Answers2