How to convert a SQL subquery query to a join sql query?

I have two tables with a 1:n relationship: "content" and "versioned-content-data" (for example, an article entity and all the versions created of that article). I would like to create a view that displays the top version of each "content".

Currently I use this query (with a simple subquery):

FROM mytable as t1
WHERE (version = (SELECT MAX(version) AS topversion
                  FROM mytable
                  WHERE (fk_idothertable = t1.fk_idothertable)))
The subquery is actually a query to the same table that extracts the highest version of a specific item. Notice that the versioned items will have the same fk_idothertable.

In SQL Server I tried to create an indexed view of this query but it seems I'm not able since subqueries are not allowed in indexed views. So... here's my question... Can you think of a way to convert this query to some sort of query with JOINs?

It seems like indexed views cannot contain:

common table expressions
derived tables
HAVING clauses

asked Jun 9, 2015 in MYSQL by rajesh
0 votes

1 Answer

0 votes
This probably won't help if table is already in production but the right way to model this is to make version = 0 the permanent version and always increment the version of OLDER material. So when you insert a new version you would say:

 UPDATE thetable SET version = version + 1 WHERE id = :id
 INSERT INTO thetable (id, version, title, ...) VALUES (:id, 0, :title, ...)
Then this query would just be

 SELECT id, title, ... FROM thetable WHERE version = 0
No subqueries, no MAX aggregation. You always know what the current version is. You never have to select max(version) in order to insert the new record.
answered Jun 9, 2015 by rajesh