AnsweredAssumed Answered

Help SQL query on the EPDM data base

Question asked by Jeremy Ferguson on Jul 29, 2013
Latest reply on Jul 30, 2013 by Jeremy Ferguson


This is a total SQL question but as it is relating to the EPDM database I am starting my search for help with this post.

I have an INNER JOIN on the “VariableValue” and “DocumentsInProjects” tables to get all of the document in a project.

OK to this point

I now have a list of all the Variable Value that are linked to a document ID with each variable having its own ID?

The problem that I face is that if VariableID 100 is the client address and that value has been change I have 2 instances of VariableID one with a RevisionNo value on 1 and one with a RevisionNo value of 2.


The big Q

How do I get only the VariableValue relating to the last revision?



SELECT [VariableID]











  FROM [PDMWE_BluewaterDMS].[dbo].[VariableValue]

  INNER JOIN [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects]

  ON [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[DocumentID]=[PDMWE_BluewaterDMS].[dbo].[VariableValue].[DocumentID]


  where [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[ProjectID]='895'

  and [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[Deleted]='0'