SQL - selecting values from one table based on values from another tab 1

[Oliver2003]

I have been using the sql below to select the DocNo field from table A and the maximum RevNo for this DocNo from table B.

This sql was posted by Golom in a previous thread701-626707

Select A.DocNo, MAX(B.RevNo) As [Max Revision]
From Docs As A INNER JOIN Revisions As B ON A.DocNo = B.DocNo
Group By A.DocN0

the sql above works great if I only want the "RevNo" form tblRevisions (table B).

Is it possible to get information from B in the row where the max revision is found e.g.

A.DocNo MAX(B.RevNo) B.Changes

so the query would display A.DocNo the maximum RevNo for this document(as posted above) and the B.Changes relating to this max RevNo

I tried just adding B.Changes to the sql above, but it displays a record for each B.Changes and not just the one that has the max B.RevNo

Thanks For Your Help

thread701-626707

 

[Diederik]

This is not the most elegant solution, but then...

SELECT A.DocNo, B.MaxRevNo, B.NoChanges
FROM Docs AS A
INNER JOIN (SELECT TOP 1 DESC RevNo AS MaxRevNo, Changes AS noChanges, DocNo FROM Revisions ORDER BY RevNo DESC) B ON B.DocNo = A.DocNo
ORDER BY DocNo

(per DocNo obtain only 1 record from them Revisions, containing the highest RevNo and the related data...)

Diederik

 

[CCLINT]

Try something like:

SELECT A.DocNo, B.RevNo, B.Changes
From Docs As A
INNER JOIN Revisions As B
ON A.DocNo = B.DocNo
WHERE B.RevNo = (SELECT MAX(B.RevNo) FROM Revisions)

 

[Oliver2003]

Thanks for the replies Diederik & CCLINT but I'm still having one or two problems,

Diederik, your sql seems to give an aggregate function error
CCLINT, your sql seems to pick only one document with the highest revision.

I may be that I am typing it in wrong!

I will try to explain it again using the proper tables and fields and would most appreciate your help

Ok, - there are two tables

The first table is called 'tblDocuments' which has the fields 'DocumentAutoID', 'DocumentNumber' and 'DocumentTitle'

The second table is called 'tblRevision' which has the fields 'DocumentAutoID', 'RevisionCode' and 'ChangesMade'

The tables are joined with a one(tblDocuments) to many(tblRevision) relationship on the 'DocumentAutoID' field.


I would like to display every 'DocumentNumber' from 'tblDocuments' and only its highest 'RevisionCode' and the 'ChangesMade' with this revision code from 'tblRevision'

which is basically what Diederik put above

below is the sql I was using to get the 'DocumentNumber' and highest 'RevisionCode' but cannot get it to work if I include 'ChangesMade'

SELECT [tblDocuments].[DocumentNumber], Max([tblRevision].[RevisionCode]) AS [Max Revision]
FROM tblDocuments INNER JOIN tblRevision ON [tblDocuments].[DocumentAutoID]=[tblRevision].[DocumentAutoID]
GROUP BY [tblDocuments].[DocumentNumber];


Again thanks for your help

Oliver

 

[Diederik]

Oops, I made a typo (sorry):
SELECT TOP 1 DESC
should obviously be SELECT TOP 1
(the DESC command to be used after the ORDER BY-clause)

Damn, normally I test stuff that like in the SQL Query Analyzer, not top of my head and this is a fine example why



Diederik

 

[Oliver2003]

Diederik, thanks for the reply but i'm still having some problems!!!

where you have B.MaxRevNo should that be MAX(B.RevNo)?

using your example I have the following:

SELECT tblDocuments.DocumentNumber, tblRevision.MaxRevisionCode, tblRevision.ChangesMade
FROM tblDocuments AS tblDocuments
INNER JOIN (SELECT TOP 1 RevisionCode AS MaxRevisionCode, Changes AS ChangesMade, FROM tblRevision ORDER BY RevisionCode DESC) tblRevision ON tblRevision.DocumentAutoID = tblDocuments.DocumentAutoID
ORDER BY DocumentNumber

I am now getting reserved word or argument name misspelled

The fields in the tables are listed in the post above

any other ideas?

Cheers
Oliver

 

[Oliver2003]

Still no joy yet, I have the following sql working that will get every DocumentNumber and its latest revision but cannot get any other info for that revision:

SELECT [tblDocuments].[ProjectDocumentNumber], MAX([tblRevision].[RevisionCode]) AS [Max Revision]
FROM tblDocuments AS tblDocuments INNER JOIN tblRevision AS tblRevision ON [tblDocuments].[DocumentAutoID]=[tblRevision].[DocumentAutoID]
GROUP BY [tblDocuments].[ProjectDocumentNumber];

CCLINT your sql seems to work but only picks out one document is there a way to go through every document.

any more help would much be appreciated

 

[Diederik]

OK, maybe this will get you somewhere:

SELECT A.DocNo, B.MaxRevNo, B.NoChanges
FROM Docs AS A
LEFT JOIN (SELECT T.MaxRevNo, DocNo, NoChanges 
           FROM Revisions
           LEFT JOIN (SELECT MAX(RevNo) AS MaxRevNo, DocNo
                      FROM Revisions
                      GROUP BY DocNo
                     ) T ON T.DocNo = Revisions.DocNo
           WHERE RevNo = T.MaxRevNo
          ) B ON B.DocNo = A.DocNo
ORDER BY DocNo

Horrible... determine the maximum revision number for a given document number, then use that maximum to obtain the remainder of the information from the revision table, and everything joined to the document table...

Although I'am in favour of normalisation of tables, this is a case where I would add a 'CurrentRevision' to the main table...

Diederik

 

[Oliver2003]

Thanks for your reply,
I did have a currentRevision field in the documents table but thought I would be smart and do it this way! - I think this field will be coming back shortly!

It still doesn’t seem to want to work - im not sure what you mean by MaxRevNo.

Although I have managed to get it to work using three different queries shown below:

Qry1 :gets all info from tblRevision for each document

SELECT tblRevision.ChangesMade, tblRevision.RevisionCode, tblRevision.DocumentAutoID
FROM tblDocuments INNER JOIN tblRevision ON (tblDocuments.DocumentAutoID = tblRevision.DocumentAutoID);

Qry2: original query to get document number and highest revision

SELECT tblDocuments.ProjectDocumentNumber, Max(tblRevision.RevisionCode) AS [Max Revision], tblDocuments.DocumentAutoID
FROM tblDocuments INNER JOIN tblRevision ON tblDocuments.DocumentAutoID = tblRevision.DocumentAutoID
GROUP BY tblDocuments.ProjectDocumentNumber, tblDocuments.DocumentAutoID;

Qry3: the required result

SELECT Qry2.ProjectDocumentNumber, Qry2.[Max Revision], Qry1.ChangesMade
FROM Qry2 INNER JOIN Qry1 ON (Qry2.DocumentAutoID = Qry1.DocumentAutoID) AND (Qry2.[Max Revision] = Qry1.RevisionCode);

But cannot combine it into one sql query!!!!

Again thanks for your help and a star for my appreciation on sticking with this one!

 

[jozino01]

Can you use those working queries as input for another query so you can get results running that one query?

 

[Oliver2003]

Sorry should have explained it a bit more, qry1 and qry2 are the input queries and they are inputed into qry3 which give the required output I want. But I would like one sql query so I can use it in VB.

The above queries are in access.Any ideas how to combine them?

Cheers
Oliver

 

[Diederik]

Hmm, I did test that query on some of my tables; so maybe I got something wrong by translating it back to the example..

LEFT JOIN (SELECT MAX(RevNo) AS MaxRevNo, DocNo
                      FROM Revisions
                      GROUP BY DocNo
                     ) T ON T.DocNo = Revisions.DocNo

This is supposed to give you the maximum Revisions.Revno per document (DocNo). MaxRevNo is 'just' an alias name for the column.

SELECT T.MaxRevNo, DocNo, NoChanges 
           FROM Revisions
           LEFT JOIN (SELECT MAX(RevNo) AS MaxRevNo, DocNo
                      FROM Revisions
                      GROUP BY DocNo
                     ) T ON T.DocNo = Revisions.DocNo
           WHERE RevNo = T.MaxRevNo

Here we obtain the data from Revisions where the RevNo is the maximum availabe RevNo; iso SELECT T.MaxRevNo you could also use RevNo (doesn't matter, they will be the same)
And then just join it with the document table.

(and it seems to me that this is the way to go to combine your queries into 1)

Hope this clarefies it a bit..

Diederik

 

[Oliver2003]

Diederik, it is probably me getting something wrong in the translation as some of my tables/fields now have different names.

I think it is the 'A', 'B', 'Docs' and 'Revisions' that are confusing me, also what is T?
I have the tables joined on DocumentAutoID (just a auto number)not DocNo (but require DocNo in the end result)

I am changing the values of 'A', 'B', 'Docs' and 'Revisions' for:

tblDocuments = 'Docs' = 'A'
&
tblReision = 'Revisions' & 'B'

I'm I doing this right? but still not sure what 'T' is?

Thank You
Oliver

 

[Diederik]

Oliver,

I just sticked to your original query/question.
'A' is an alias for table Docs (which you now call tblDocuments)
'B' is an alias for table Revisions (which you now call tblRevisions)
(so you got that one right)
'T' is an alias for the subquery result, so that it can be used in the "overall" query.

Using DocumentAutoID or Docno, it's just a key value

Then again, I just wiggle around in SQL, not using Access, so it could be that the differences between statement handling gets in the way..

I still suggest that you put in your main document table a field "CurrentRevision", that will make the query easier to construct AND will give performance gain when running

Diederik

 

[Oliver2003]

Diederik I have been playing around again with your sql and this time I got it to work!!!!!!

Initially I was getting the error that names could refer to mare than one table so I put the name of each table infront of its field name - and it works!

Thank you very much for helping me with this one.

Oliver.