Put two queries together

  • Hello:

    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.

     

    Thanks!

    Steve Anderson

  • Maybe this

    SELECT D.DocumentID AS [Document ID], D.Filename AS [Document Filename], 
    XR.XRefDocument As [Child Document ID], D2.Filename AS [Child Filename],
    MAX(XR.RevNr) As [Rev Nr]
    FROM Documents As D
    JOIN XRefs AS XR ON D.DocumentID = XR.DocumentID
    join Documents as D2 on xr.XRefDocument=d2.DocumentID
    WHERE D.Deleted = 0
    And D.DocumentID = 706474
    GROUP BY D.DocumentID, D.Filename, XR.XRefDocument, D2.Filename
    ORDER BY XR.XRefDocument;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thank you!

     

    Steve Anderson

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply