I created a View that makes a table showing a document's file name, revision variable, document ID, project ID, cleaning variable, revision number, and part number variable. However, there seems to be a row for each value of these variables.
Here's my View:
SELECT dbo.Documents.Filename, VariableValue_1.ValueText AS [DRW Rev], dbo.Documents.DocumentID, dbo.DocumentsInProjects.ProjectID,
VariableValue_2.ValueText AS [Cleaning Required], VariableValue_1.RevisionNo, dbo.VariableValue.ValueText AS [Part Number]
FROM dbo.Documents INNER JOIN
dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN
dbo.Variable ON dbo.VariableValue.VariableID = dbo.Variable.VariableID INNER JOIN
dbo.VariableValue AS VariableValue_1 ON dbo.Documents.DocumentID = VariableValue_1.DocumentID INNER JOIN
dbo.Variable AS Variable_1 ON VariableValue_1.VariableID = Variable_1.VariableID INNER JOIN
dbo.VariableValue AS VariableValue_2 ON dbo.Documents.DocumentID = VariableValue_2.DocumentID INNER JOIN
dbo.Variable AS Variable_2 ON VariableValue_2.VariableID = Variable_2.VariableID INNER JOIN
dbo.DocumentsInProjects ON dbo.Documents.DocumentID = dbo.DocumentsInProjects.DocumentID
WHERE (dbo.Documents.Filename LIKE '%.slddrw') AND (dbo.Variable.VariableName LIKE 'Part Number') AND (dbo.VariableValue.ValueText LIKE 'RBT-%') AND
(Variable_1.VariableName LIKE 'Revision') AND (Variable_2.VariableName LIKE 'Cleaning Required') AND (dbo.Documents.Deleted = 0) AND
(dbo.Documents.Shared <> 0) AND (dbo.Documents.DocTypeID = 1) AND (NOT (VariableValue_1.ValueText LIKE '')) AND (NOT (VariableValue_2.ValueText LIKE '')) AND
(NOT (dbo.VariableValue.ValueText LIKE ''))
Here is the query that uses it:
select * from dbo.drawingbypartno
where [Part Number] like '%-00B876%'
order by RevisionNo desc
Here are my results:
What I want to have is something that shows one line for each RevisionNo and it should be tied to the corresponding values for the file that is at that revision. In the image above, the correct line for RevionNo 12 is line 5. Looking into SQL commands, I think I need to use the UNION command, but I can't quite figure out how to format it.
Any help would be appreciated.