Right now I have two queries as shown below:
SELECT D.DocumentID AS [Document ID], D.Filename AS [Document Filename], XR.XRefDocument As [Child Document ID], MAX(XR.RevNr) As [Rev Nr]
FROM Documents As D INNER JOIN
XRefs AS XR ON D.DocumentID = XR.DocumentID
WHERE(D.Deleted = 0) And (D.DocumentID = 706474)
GROUP BY D.DocumentID, D.Filename, XR.XRefDocument
ORDER BY XR.XRefDocument
SELECT D2.Filename AS [Child Filename] FROM Documents as D2 INNER JOIN
XRefs AS XR2 ON D2.DocumentID = XR2.DocumentID
WHERE D2.DocumentID = XR2.DocumentID
GROUP BY D2.Filename, XR2.XRefDocument
My goal is to have the second query be another column for the first, becayse the Filename field only exists on Documents, though the FileID exists both in the Documents and XRef tables.
I think I'm close.