Post reply

Put two queries together

  • July 26, 2021 at 9:35 pm

    #3914096

    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

  • July 27, 2021 at 10:02 am

    #3914237

    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

  • July 27, 2021 at 2:51 pm

    #3914333

    Thank you!

     

    Steve Anderson

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

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