Something like this:
Function Get-PropertiesFromTable [
params(
[string]$tableName = 'Article'
[string]$breadcrumb
[int]PropertyId
)
Here you can select all objects, i.e. records, that are available in TableName at the node. All subfolders are recursively traversed until all property containers are found and the IDs of the objects are collected. Duplicates are filtered.
;WITH Tree(ID) AS
(
SELECT ID
FROM Merkmal
WHERE ID = 1002 -- Starting point of the tree
AND Tabelle = 'Artikel'
AND MerkmalTyp <> 2
UNION ALL
SELECT m.ID
FROM Merkmal m
INNER JOIN Tree t ON m.ParentID = t.ID
WHERE m.Tabelle = 'Artikel'
AND m.MerkmalTyp <> 2
)
SELECT DISTINCT me.ObjektId [Id]
FROM MerkmalElement me
INNER JOIN Tree t ON me.KopfId = t.ID;
Something like this: Function Get-PropertiesFromTable [ params( [string]$tableName = 'Article' [string]$breadcrumb [int]PropertyId )
Here you can select all objects, i.e. records, that are available in TableName at the node. All subfolders are recursively traversed until all property containers are found and the IDs of the objects are collected. Duplicates are filtered.
;WITH Tree(ID) AS ( SELECT ID FROM Merkmal WHERE ID = 1002 -- Starting point of the tree AND Tabelle = 'Artikel' AND MerkmalTyp <> 2 UNION ALL SELECT m.ID FROM Merkmal m INNER JOIN Tree t ON m.ParentID = t.ID WHERE m.Tabelle = 'Artikel' AND m.MerkmalTyp <> 2 ) SELECT DISTINCT me.ObjektId [Id] FROM MerkmalElement me INNER JOIN Tree t ON me.KopfId = t.ID;