CODEWITHSUNDEEP
sqlsql-serverjoinsubquery
sachaa
sachaa

Reputation: 1987

How to convert a SQL subquery to a join

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".

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 to the same table that extracts the highest version of a specific item. The versioned items will have the same fk_idothertable.

In SQL Server I tried to create an indexed view of this query but subqueries are not allowed in indexed views.

What is a way to convert this query to one with JOINs?

It seems like indexed views cannot contain:

Upvotes: 9

Views: 32969

Answers (7)

Thava
Thava

Reputation: 1665

If SQL Server accepts a LIMIT clause:

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1
ORDER BY t1.version
DESC LIMIT 1;

(DESC for descending sort.)

LIMIT 1 chooses only the first row and a DBMS usually does good optimization.

Upvotes: 0

Chris Shaffer
Chris Shaffer

Reputation: 32575

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

Upvotes: 0

jmucchiello
jmucchiello

Reputation: 18984

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.

Upvotes: 14

yothenberg
yothenberg

Reputation: 1178

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

Upvotes: 0

jpalecek
jpalecek

Reputation: 47762

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...

Upvotes: 3

Charles Graham
Charles Graham

Reputation: 24835

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

Upvotes: 0

James
James

Reputation: 3362

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

Upvotes: -2

Related Questions