Organizational Research By

Surprising Reserch Topic

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

Related Hot Questions

1 Answer

0 votes
answered Jun 9, 2015 by rajesh