SQL: union, max, join

Question

I am trying to create a document database, which is built up by the following main tables:

• tblProjects
• tblDocuments (each project holds a number of documents)
• tblRevisions (each document holds a number of revisions)

I want to set up a main overview list: which shows every project with every document in my database, but only each document's latest revision info.

here's my relationships setup

To achieve my goal, I've understood that I must create a set of different queries.

A) Show both all the documents and the projects they belong to within the same list:

Example:

    Project 1
    Document 1.1
    Document 1.2
    Project 2
    Document 2.1
    Document 2.2

To achieve this, I made the union query "quniProjectsDocuments":

    SELECT ProjectID,0 as [0/DocID],ProjectTitle
    FROM tblProjects

    UNION ALL 

    SELECT ProjectID,DocID,DocTitle
    FROM tblDocuments

    ORDER BY ProjectID, [0/DocID];

And it works...

B) Only show the latest revision info of each document:

That's easy enough, right? Just SELECT tblDocuments.DocID, tblDocuments.DocTitle etc. and Max(tblRevisions.OurRev)

Well sure, that works (kind of). It only works as long as tblRevisions.OurRev is the only field I SELECT from tblRevisions. If I add more of the "revision info" (i.e. tblRevisions.ClientRev) I get a messed up list of ALL revision records in addition to the maxed ones.

C) Combine everything

How can I achieve the following?:

   Project 1
   Document 1.1 | Our(Latest)Rev | ClientRev
   Document 1.2 | Our(Latest)Rev | ClientRev
   Project 2
   Document 2.1 | Our(Latest)Rev | ClientRev
   Document 2.2 | Our(Latest)Rev | ClientRev

EDIT 22.02.2016:

Jcollerton: "I think what you're going to find is you have to write a really complicated query to get the data in the exact format you want at the moment."

RE: Can't I combine 2-3 "simple" queries though - and make them work together? As said above, I already have the union query working (which achieves my goal of listing both projects and documents in the same column (and also sorts the docs by the projects they belong to)).

I've also managed to get a list of all docs with the latest revision information by the use of two different queries: one MAX -query and one JOIN query. As follows (these I made prior to creating the union query by using the design view:

1) MAX query " qryLastRev"=

    SELECT [tblDocuments].DocID, Max([tblRevisions].BSNRev) AS MaxOfBSNRev
    FROM tblDocuments 
    INNER JOIN tblRevisions ON [tblDocuments].DocID=[tblRevisions].DocID
    GROUP BY [tblDocuments].DocID;

2) JOIN query "qryDocsByLastRev"=

    SELECT tblDocuments.DocTitle, tblDocuments.OurDocNo, tblDocuments.ClientDocNo, tblRevisions.BSNRev, tblRevisions.ClientRev, tblRevisions.SubDate, tblRevisions.SubCode
    FROM tblProjects 
      INNER JOIN 
        ((qryLastRev INNER JOIN tblDocuments ON qryLastRev.DocID = tblDocuments.DocID) 
        INNER JOIN tblRevisions ON (tblDocuments.DocID = tblRevisions.DocID) 
        AND (qryLastRev.MaxOfBSNRev = tblRevisions.BSNRev)) 
      ON tblProjects.ProjectID = tblDocuments.ProjectID;

The result is a list of all documents and their doc numbers from tblDocuments - and only the latest revision info from tblRevisions.

    DocTitle | DocNo | Rev | SubDate | SubCode

Can it be so hard to combine the union query and the max/join queries together? Not necessarily by combining them into one single query, but by making them work together?

Answer 1

Do you use a GROUP BY clause with that MAX(tblRevisions.OurRev)? I would need to see the full query you've tried to get your end result to post a more complete answer.

Answer 2

You could do this using:

SELECT
    ProjectTitle, DocTitle, ClientRev
FROM
    tblProjects
INNER JOIN
    tblDocuments
ON
    tblProjects.ProjectID = tblDocuments.ProjectID
INNER JOIN
    tblRevisions
ON
    tblDocuments.DocID = tblRevisions.DocID
INNER JOIN
    (SELECT DocID, MAX(SubDate) FROM tblDocuments GROUP BY DocID) groupedDoc
ON
    tblDocuments.DocID = groupedDoc.DocID

This will return:

Project 1 | Document 1.1 | ClientRev
Project 1 | Document 1.2 | ClientRev
Project 2 | Document 2.1 | ClientRev
Project 2 | Document 2.2 | ClientRev

Where the client review is the latest one.

It will be possible to get it in the form:

Project 1
Document 1.1 | ClientRev
Document 1.2 | ClientRev
Project 2
Document 2.1 | ClientRev
Document 2.2 | ClientRev

But there are two things. Firstly, you have two columns here, one for your document and one for your client review. You will need to choose something to put in the second column when you only have the project name. So for example you could have:

Project 1    | NULL
Document 1.1 | ClientRev
Document 1.2 | ClientRev
Project 2    | NULL
Document 2.1 | ClientRev
Document 2.2 | ClientRev

The second thing is that I personally (disclaimer!) wouldn't say that this is the best way to use SQL. SQL is designed to give you data as a table, with columns representing different attributes. You would then take data from this table and format it in your application, rather than try and do the formatting using a SQL query. I think that this approach will be a lot easier for what you are trying to achieve!

Anyway, to get it in the format you want, you could try:

SELECT
    ProjectTitle, NULL, ProjectID
FROM
    tblProjects

UNION ALL

SELECT
    DocTitle, ClientRev, ProjectID
FROM
    tblDocuments
INNER JOIN
    tblRevisions
ON
    tblDocuments.DocID = tblRevisions.DocID
INNER JOIN
    (SELECT DocID, MAX(SubDate) FROM tblDocuments GROUP BY DocID) groupedDoc
ON
     tblDocuments.DocID = groupedDoc.DocID

ORDER BY
     ProjectID