Subquery within LIKE condition

Question

Suppose I have the following hierarchical table:

+-------+----------+-------+-------------------------+-----------------+--------+
| ID    | ParentID |  Name |           Path          | InheritanceFlag | ToEdit |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76938 | NULL     |   1   |         (76938)         | 1               | X      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76942 | 76938    |  1.1  |     (76938)\(76942)     | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76952 | 76942    | 1.1.1 | (76938)\(76942)\(76952) | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76961 | 76942    | 1.1.2 | (76938)\(76942)\(76961) | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76943 | 76938    |  1.2  |     (76938)\(76943)     | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76944 | 76938    |  1.3  |     (76938)\(76944)     | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76946 | 76944    | 1.3.1 | (76938)\(76944)\(76946) | 1               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76947 | 76944    | 1.3.2 | (76938)\(76944)\(76947) | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76948 | 76944    | 1.3.3 | (76938)\(76944)\(76948) | 1               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76945 | 76938    | 1.4   | (76938)\(76945)         | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+

My input is a table of IDs (it's a JSON string that I converted) and I want to select those IDs and their descendance (children and children's children, etc). For example, if the IDs are 76942 and 76946, it should return me the rows the following rows:

+-------+----------+-------+-------------------------+-----------------+--------+
| ID    | ParentID |  Name |           Path          | InheritanceFlag | ToEdit |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76942 | 76938    |  1.1  |     (76938)\(76942)     | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76952 | 76942    | 1.1.1 | (76938)\(76942)\(76952) | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76961 | 76942    | 1.1.2 | (76938)\(76942)\(76961) | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76946 | 76944    | 1.3.1 | (76938)\(76944)\(76946) | 1               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+

How can I manage to create this query ? I used to do it with a simple LIKE with the Path, but since it's now a list, I can't use it.

SELECT  
    [pkID]
FROM    
    [dbo].[t_Activites]
WHERE   
    [sFullPath] LIKE CONCAT('%(', @id, ')%')

For those interested in the CROSS APPLY solution, here it is. I ran the Execution Plan and it is more efficient.

SELECT  A.pkID
FROM    t_Activites A
CROSS APPLY @Ids
JOIN    t_Activites A1
    ON  A1.pkID = s.id
WHERE   A.sFullPath LIKE CONCAT('%(', A1.pkID, ')%')

You'll want the ids in a form you can JOIN to, and a recursive CTE. Unfortunately, the latter isn't something I do often enough to be able to put together a quick answer, but at least this will help point you in the right direction. — Joel Coehoorn, Oct 05 '20 at 19:48
@GMB the example using @id is a simple `INTEGER`. It was how I managed to do it for 1 ID at the time. But now, I have a list of IDs and need to treat them in a batch — RegularNormalDayGuy, Oct 05 '20 at 19:50
@RegularNormalDayGuy: so, what does this of ids look like? Is it a string? A table variable? — GMB, Oct 05 '20 at 19:51
@JoelCoehoorn Indeed, I was thinking of going with CTE, but I'm no experienced enough either. I'm trying `CROSS APPLY` at the moment, I will post if I find something that works a bit — RegularNormalDayGuy, Oct 05 '20 at 19:51
@GMB A table variable. (In reality, it's a JSON I parsed into a table variable with one column, named IDs) — RegularNormalDayGuy, Oct 05 '20 at 19:52

score 2 · Accepted Answer · answered Oct 05 '20 at 19:55

2

If you have a table variable, say @ids(id), you can use exists:

SELECT a.pkID
FROM dbo.t_Activites a
WHERE EXISTS (SELECT 1 FROM @ids i WHERE a.sFullPath LIKE CONCAT('%(', i.id, ')%'))

answered Oct 05 '20 at 19:55

GMB

216,147
25
84
135

1

It works, thanks, I managed to make it work with `CROSS APPLY`, but your solution is simpler so I will take it thanks – RegularNormalDayGuy Oct 05 '20 at 19:59

Subquery within LIKE condition

1 Answers1