CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
jltrem
jltrem

Reputation: 12524

SQL Server recursive query with associated table

I have a typical parent/child relationship table to represent folders. My challenge is using it in conjunction with another table.

The folder table is like this:

+--+----+--------+
|id|name|parentid|
+--+----+--------+
|1 |a   |null    |
+--+----+--------+
|2 |b   |1       |
+--+----+--------+
|3 |c1  |2       |
+--+----+--------+
|4 |c2  |2       |
+--+----+--------+

The association table is like this:

+--+--------+
|id|folderid|
+--+--------+
|66|2       |
+--+--------+
|77|3       |
+--+--------+

so that where association.id = 66 has a relationship to folder.id = 2

What I need to do is find the association.id of the first ancestor with a record in the association table.. Using the example data above, given folder.id of 3 I expect to find 77; given folder.id of 2 or 4 I expect to find 66; any other folder.id value would find null.

Finding folder ancestry can be done with a common table expression like this:

WITH [recurse] (id,name,parentid,lvl) AS
(
    select a.id,a.name,a.parentid,0 FROM folder AS a   
    WHERE a.id='4'
    UNION ALL
    select r.id,r.name,r.parentid,lvl+1 FROM folder as r 
    INNER JOIN [recurse] ON recurse.parentid = r.id
)
SELECT * from [recurse] ORDER BY lvl DESC

yielding the results:

+--+----+--------+---+
|id|name|parentid|lvl|
+--+----+--------+---+
|1 |a   |        |2  |
+--+----+--------+---+
|2 |b   |1       |1  |
+--+----+--------+---+
|4 |c2  |2       |0  |
+--+----+--------+---+

To include the association.id I've tried using a LEFT JOIN in the recursive portion of the CTE, but this is not allowed by SQL Server.

What workaround do I have for this?

Or better yet, is the a way to query directly for the particular association.id? (e.g., without walking through the results of the CTE query that I have been attempting)

Upvotes: 0

Views: 271

Answers (1)

jzworkman
jzworkman

Reputation: 2703

SELECT r.id, r.name, r.parentid, r.lvl, a.folderid, a.id as associationid 
FROM [recurse] r
LEFT JOIN [association] a 
ON r.id = a.folderid
WHERE a.folderId IS NOT NULL
ORDER BY lvl DESC

This will give you the records that have values in the association table. Then you could limit it to the first record that has a value or just grab the top result

Upvotes: 1

Related Questions