Looking for specific SQL query

Marcde's query works well enough if there is just a single Vault Store, but if there are multiple Vault Stores then you can't join on IdPartition alone because that counter is reset for every Vault Store. That is, VaultStore01 has a partition with IdPartition = 0, and VaultStore02 also has a partition with IdPartition = 0. So if you join it this way and you have n Vault Stores, you will get n rows for each IdTransaction you query. Example from running your query in an environment with four Vault Stores:

In order to deal with this possibility, you can join a few more tables to make sure you're returning IdPartitions only from the Vault Store to which the item you are querying belongs:

SELECT IdTransaction, 
	pe.IdPartition, 
	pe.PartitionRootPath 
FROM Saveset AS s
INNER JOIN ArchivePoint AS ap
	ON ap.ArchivePointIdentity = s.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av
	ON av.VaultEntryId = ap.ArchivePointId
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry AS vse
	ON vse.VaultStoreEntryId = av.VaultStoreEntryId
INNER JOIN [EnterpriseVaultDirectory].[dbo].PartitionEntry AS pe
ON s.IdPartition = pe.IdPartition AND vse.VaultStoreEntryId = pe.VaultStoreEntryId
WHERE IdTransaction = 'A118899D-BCA8-11B9-E843-943B1EE4B751' --Substitute your own item's IdTransaction here

A little tricky to explain, but hopefully helpful to somebody...

BruceCrankshaw1, if you have only one Vault Store, then you're fine to continue with Marc's query. If not, use the one above for more precise results.

--Chris

Thank Marc

Great suggestions