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

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
asked Jun 9, 2015 in MYSQL by rajesh
0 votes
41 views



Related Hot Questions



Government Jobs Opening


...