CODEWITHSUNDEEP
sqldatabaseoracle-database
hellomate
hellomate

Reputation: 133

Join on 2 tables to display the latest result Oracle, SQL

I have 2 tables in my database: da_documents and da_favourites. As you expect da_favourites is the documents added to the favourites list by a user. da_documents stores all the documents available

Each document has key columns: da_doc_id (id for set of documents), da_document_id (unique id for every document), da_version (version of the document).

da_doc_id can be shared between lot of different da_document_ids. enter image description here

I'm trying to come up with a select statement to show da_doc_id, da_document_id from da_favourites table and also the latest da_document_id for this specific da_doc_id.

Say we added da_document_id 14001 and 14002 to da_favourites but 14003 exists in da_documents I want to be able to see that latest da_document_id next to the da_document_id added to the favourites.

I have came up with a statement that shows documents that have a newer version however I'm unable to show the latest da_document_id for that document.

select da_doc_id, da_document_id
   from da_favourites o
   where da_document_id !=
          (SELECT da_document_id
           FROM   da_documents d
           WHERE  da_version IN (SELECT MAX (da_version)
                                 FROM   da_documents i
                                 WHERE  i.da_doc_id = d.da_doc_id
                                 GROUP BY i.da_doc_id)
           AND d.da_doc_id = o.da_doc_id);

@edit

I want to end up with something like this:

enter image description here

da_documents sample data

enter image description here

da_favourites sample data

enter image description here

As you can see version 4 of da_doc_id 14874 exists.

I've finally come up with this. Is there a simpler way of doing it?

SELECT dad.da_doc_id, daf.da_document_id, dad.da_document_id latest_document_id
FROM da_documents dad
JOIN da_favourites daf
ON dad.da_doc_id = daf.da_doc_id
WHERE da_version IN (SELECT MAX (da_version)
                     FROM   da_documents dad2
                     WHERE  dad2.da_doc_id = dad.da_doc_id
                     GROUP BY dad2.da_doc_id)
AND dad.da_document_id != daf.da_document_id
AND dad.da_doc_id IN (SELECT da_doc_id
                      FROM da_favourites);

Upvotes: 0

Views: 46

Answers (2)

Robbie Chiha
Robbie Chiha

Reputation: 409

If it is used frequently I would create a view

CREATE VIEW vw_doc_version AS
SELECT da_doc_id, MAX(da_version) latest_version
FROM da_documents
GROUP BY da_doc_id

Your sql would then be simple

SELECT f.da_doc_id, f.da_document_id, d.da_document_id AS latest_document_id
FROM da_favourites f
INNER JOIN vw_doc_version dv ON dv.da_doc_id = f.da_doc_id
INNER JOIN da_documents d ON d.da_doc_id = dv.da_doc_id 
AND d.da_version = dv.latest_version

Otherwise you could do it with a derived table

SELECT f.da_doc_id, f.da_document_id, d.da_document_id AS latest_document_id
FROM da_favourites f
INNER JOIN (SELECT da_doc_id, MAX(da_version) latest_version
    FROM da_documents
    GROUP BY da_doc_id) dv
ON dv.da_doc_id = f.da_doc_id
INNER JOIN da_documents d
ON d.da_doc_id = dv.da_doc_id
AND d.da_version = dv.latest_version

or cte

with cte as (SELECT da_doc_id, MAX(da_version) latest_version
    FROM da_documents
    GROUP BY da_doc_id)
SELECT f.da_doc_id, f.da_document_id, d.da_document_id AS latest_document_id
FROM da_favourites f
INNER JOIN cte dv
ON dv.da_doc_id = f.da_doc_id
INNER JOIN da_documents d
ON d.da_doc_id = dv.da_doc_id
AND d.da_version = dv.latest_version

Excuse any syntax or formatting errors as I don't have an oracle background.

Feedback is welcome.

Edited to include document id of latest version

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

You may use window functions for this

  SELECT o.*, t.da_document_id
  FROM da_favourites o
  JOIN
  (
       SELECT i.*, row_number() over (partition by i.da_doc_id order by da_version desc) rn
       FROM   da_documents i
  ) t ON  t.da_doc_id= o.da_doc_id and 
          t.rn = 1 and
          t.da_document_id != o.t.da_document_id

Upvotes: 0

Related Questions