There are 3 tables (syntax errors are possible, correct, who has the opportunity now to check the correctness of writing)

CREATE TABLE t1 (id int) GO INSERT INTO t1 (id) VALUES (1) GO CREATE TABLE t2 (id int ,t1_id int ,t3_id int) GO INSERT INTO t2 (id,t1_id,t3_id) VALUES (1,1,1) ,(2,1,1) GO CREATE TABLE t3 (id int ,parent int NULL ,name varchar(50)) GO INSERT INTO t3 (id, parent) VALUES (1,null, 'node_1') (2,null, 'node_2') (3,1, 'node_1_1') (4,2, 'node_2_1')

The original task was to print all the lines from t1 (in this case it is 1 line). At the same time, connect the table t2 and t3 in order to output all t3.name in the line that refer to t1.id The task is not complicated, I merged strings through FOR XML PATH . But there was an additional task, you need to search for t3.id , and going deep into the tree. That is, if for the search I set t3.id = 3 I also have to get this single row, which is in table t1 , because this element is the heir of the tree node with t3.id = 1 . I tried to solve this task by writing a function that, according to the transmitted t3.id, will determine if the current id matches the condition, but the filter with this function works very slowly (there are a lot of records). Therefore, I decided to build a query so that, according to a given example, such a result would turn out

t1.id / t2.id / t3.id

1/1/1

1/1/2

1/2/1

1/2/2

in this case, I can easily search the entire depth of the tree. It remains to figure out how to build such a request. Well, first you need a recursion. Here again there are 2 options.

  1. Create a function that, according to the transmitted t3.id, returns a table with all its children id and connect it via CROSS APPLY .
  2. Use recursion directly in the query.

I don’t like option 1, because the table returned by the function will not be indexed, which means that the sample will also be slow.

Option 2 I do not know how to implement, because it is impossible to insert WITH into CROSS APPLY (SELECT is expected there immediately)

Do you have any thoughts on this? Or maybe someone came across and have a ready-made solution?

  • Can you add the desired issue to the question? - 4per
  • @ 4per, she is there - iRumba
  • there are only root values ​​from t3 (1 and 2), and how do you want to use the following levels? - 4per
  • one
    You would ask for your request with cross apply, of course working on the tables given in the example would be written here. Then it would be clear what it was about and what needs to be finalized. For any with you can write before the request, and in the request itself use it ... - Mike
  • one
    There are two types of table functions: inline and multi-statement . If inline is used, there is no difference between using a function and direct substitution of an expression from a function into a query (the query processor actually does the same thing). - i-one

0