How to select related records from tables?

Question

PHP

Hello,
There are three linked tables for documents, their versions and statuses: Document(id, title), DocumentRevision(id, time, documentId) and RevisionStatus (id, time, revisionId, status). Statuses can be: 0-new, 1-sent for review, 2-rejected, 3-accepted. Accordingly, for the document, the current status is the last revision status. And if you individually select a document, its versions and statuses is not a problem, then please tell me how to select all the documents sent for verification?

upd:
Thank you all, although you can solve the problem with a complex query, I decided to denormalize the database - it's easier to work and track.

Answer 1

select d.* from Document as d
join DocumentRevision as dr on d.id = dr.documentId
join RevisionStatus as rs on dr.id = rs.revisionId
where rs.status = 1

Answer 2

The current diagram looks like this:

SELECT d.* FROM Document d JOIN DocumentRevision r ON r.id = (SELECT MAX(id) FROM DocumentRevision WHERE documentId = d.id) JOIN RevisionStatus s ON s.id = (SELECT MAX(id) FROM RevisionStatus WHERE revisionId = r.id) AND s.status = 1

In general, denormalize - copy the last status and revision number to Document, trigger or something else.

Answer 3

Something like this:

SELECT title FROM document AS d
INNER JOIN documentrevision AS dr ON dr.documentid=d.id
WHERE dr.id = (SELECT revisionid FROM revisionstatus WHERE status=1);

Answer 4

I support, be sure to denormalize. Triggers in the application are absolutely normal, in the database they are rather exotic.
And here is a variant without subqueries (assuming datetime type for time fields):

select
  substr(max(concat(
    DocumentRevision.time, ":",
    RevisionStatus.time, ":",
    RevisionStatus.status, ":",
    Document.id
  )), 43) docid
from
  Document, DocumentRevision, RevisionStatus
where
  DocumentRevision.documentId = Document.id and
  RevisionStatus.revisionId = DocumentRevision.id
group by
  Document.id
having
  substr(max(concat(
    DocumentRevision.time, ":",
    RevisionStatus.time, ":",
    RevisionStatus.status, ":",
    Document.id
  )), 41, 1) = "1"