We are trying to convert this SQL from v7 to equivalent in v10, where DLF is a custom table for a custom section.

SELECT dbo.DLF.dlf_dlvId,  dbo.umbracoNode.text AS dlf_fileName, dbo.cmsPropertyData.dataNvarchar AS dlf_url
FROM        (lots of joins here for products/variations/download files)
             INNER JOIN  dbo.umbracoNode ON dbo.umbracoNode.id = dbo.DLF.dlf_fileNodeId 
        INNER JOIN  dbo.cmsPropertyData ON dbo.cmsPropertyData.contentNodeId = dbo.DLF.dlf_fileNodeId AND dbo.cmsPropertyData.propertytypeid = 24

Files are uploaded via a custom section but essentially are Media section nodes. The cmsPropertyData table does not exist anymore. In some ways, the dbo.umbracoPropertyData table is similar but it's not an exact match and I can't seem to link them all up anymore. I can't find a new database schema. For example, custom section has a downloadable file in DLF table with a fileNodeId of 4383 which matches up with the umbracoNode id and the id of the node in the Media folder for the relevant document. So I think that's been saved correctly. However I need to get the filepath via SQL (the client wants SQL Views to set up on their extranet or something). The two tables I can find which will give me the filepath, eg "/media/2syoa44q/brochure.pdf" are umbracoMediaVersion and umbracoPropertyData. In uMV the id = 3686 and the same with the uPD table - it has id = 22326, version id = 3686 proptypeid = 46 and then varcharValue = the path. Can't see how to to link these tables up so that I can grab the path if I have the fileNodeId from DLF - even via another table.

We do have some really nice ways of querying the data through an API that runs on a cache so it's performant! 😅 But here's a sample query to find all the node Ids of all the nodes with an

umbracoFile

property on it (that would be

dbo.cmsPropertyData.propertytypeid = 24

in your previous query).

sql
SELECT nodeId FROM [dbo].[umbracoContentVersion]
WHERE id IN (
SELECT [versionId] FROM [dbo].[umbracoPropertyData]
  WHERE propertyTypeId IN (
      SELECT [id] FROM [dbo].[cmsPropertyType] WHERE alias = 'umbracoFile'
    )
)

umbracoNode.Text

is not necessarily the filename by the way, the

textvalue

in

umbracoPropertyData

is the file name. I am sure you'll have follow-up questions but this is far as my SQL knowledge goes at the moment, I'd encourage you to investigate using our APIs for finding media nodes (not

MediaService

, that one does database queries). Not sure what your WHERE clause looks like though, so I might be off here,.

The problem is the client wants it via SQL Server Views so he can link his extranet up with it - it's already working like that on the u7 site, he has his own login that just gives access to the views - so it's not through the website at all, just the SQL. But thank you, I will have a look at this.

Oh I see, well.. good luck! 😅 Sorry, don't have much more to offer, we generally don't see direct SQL queries as an API into umbraco, so things move around sometimes!

Only got back to this today - thank you though, it gave me a new angle to start from and I seem to be getting the same data out so hopefully client will be happy with it.