How to convert a SQL subquery to a join

Question

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):

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
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:

• subqueries
• common table expressions
• derived tables
• HAVING clauses

I'm desperate. Any other ideas are welcome :-)

Thanks a lot!

Answer 1

Like this...I assume that the 'mytable' in the subquery was a different actual table...so I called it mytable2. If it was the same table then this will still work, but then I imagine that fk_idothertable will just be 'id'.

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1
    INNER JOIN (SELECT MAX(Version) AS topversion,fk_idothertable FROM mytable2 GROUP BY fk_idothertable) t2
     ON t1.id = t2.fk_idothertable AND t1.version = t2.topversion

Hope this helps

Answer 2

I don't know how efficient this would be, but:

   SELECT t1.*, t2.version
   FROM mytable AS t1
       JOIN (
           SElECT mytable.fk_idothertable, MAX(mytable.version) AS version
           FROM mytable
       ) t2 ON t1.fk_idothertable = t2.fk_idothertable

Answer 3

You Might be able to make the MAX a table alias that does group by.

It might look something like this:

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1 JOIN
   (SELECT fk_idothertable, MAX(version) AS topversion
   FROM mytable
   GROUP BY fk_idothertable) as t2
ON t1.version = t2.topversion

Answer 4

Maybe something like this?

SELECT
  t2.id,
  t2.title,
  t2.contenttext,
  t2.fk_idothertable,
  t2.version
FROM mytable t1, mytable t2
WHERE t1.fk_idothertable == t2.fk_idothertable
GROUP BY t2.fk_idothertable, t2.version
HAVING t2.version=MAX(t1.version)

Just a wild guess...

Answer 5

I think FerranB was close but didn't quite have the grouping right:

with
latest_versions as (
   select 
      max(version) as latest_version,
      fk_idothertable
   from 
      mytable
   group by 
      fk_idothertable
)
select
  t1.id, 
  t1.title, 
  t1.contenttext,
  t1.fk_idothertable,
  t1.version
from 
   mytable as t1
   join latest_versions on (t1.version = latest_versions.latest_version 
      and t1.fk_idothertable = latest_versions.fk_idothertable);

M

Answer 6

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.