Ask a Question
Advertise on boostr.in
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
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†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
Jun 9, 2015
to add a comment.
Related Hot Questions
Government Jobs Opening