Help with attachment_map table
Hello All –
I am attempting to write a SQL query which provides the URL to download an attachment when passing an item number as a criteria/parameter. I think I am really close, but something is not quite right with one of my joins and I am getting more URLs than expected. I am limiting to the latest rev, via the REV table, the output shows only the latest rev, however I am getting URLs for all rev’s listed. I think the problem is that the attachment_map is joined to the item table.
I don’t see an obvious way to correlate which attachments are for which revs. Any help would be fantastic.
Broken SQL query as follows…(12345 is just a random p/n)
SELECT AGILE.ITEM.ITEM_NUMBER,
AGILE.REV.REV_NUMBER,
AGILE.FILES.FILENAME,
AGILE.FILES.CONTENT_URL,
AGILE.REV.LATEST_FLAG
FROM AGILE.ATTACHMENT_MAP
INNER JOIN AGILE.VERSION_FILE_MAP
ON AGILE.ATTACHMENT_MAP.LATEST_VSN = AGILE.VERSION_FILE_MAP.VERSION_ID
INNER JOIN AGILE.FILES
ON AGILE.VERSION_FILE_MAP.FILE_ID = AGILE.FILES.ID
INNER JOIN AGILE.ITEM
ON AGILE.ATTACHMENT_MAP.PARENT_CLASS = AGILE.ITEM.CLASS
AND AGILE.ITEM.ID = AGILE.ATTACHMENT_MAP.PARENT_ID
INNER JOIN AGILE.REV
ON AGILE.REV.ITEM = AGILE.ITEM.ID
WHERE AGILE.ITEM.ITEM_NUMBER = ‘12345’
AND AGILE.REV.LATEST_FLAG = 1
You are close, very close.
Add the following line to your where clause : AND AGILE.ATTACHMENT_MAP.PARENT_ID2 = AGILE.REV.CHANGE
The PARENT_ID2 attribute in ATTACHMENT_MAP holds the ID of the change that released the revision to which the file is associated. Including the above in the where clause will make sure that you only get files linked to the latest revision. Put it under the inner join with AGILE.REV, and things should work just fine.