How to select parents and children rows in SQL Server

Question

I want to select all itens related to user_id 53 (parents and children) from the following table. It should be: 1, 2, 4, 8, 9.

my_table
--------------------------------------------
id    parent_id   user_id   sequence   depth
--------------------------------------------
1     null        50        1          1
2     1           52        1.2        2
3     1           52        1.3        2
4     2           53        1.2.4      3
5     2           52        1.2.5      3
6     3           52        1.3.6      3
7     3           51        1.3.7      3
8     4           51        1.2.4.8    4
9     4           51        1.2.4.9    4

With CTE I could select all children or parents, but I could'nt select children and parents with just one query. Below is the cte I'm using to select children.

Item and children

with cte as (
    select t.id, t.parent_id, t.user_id 
    from my_table t 
    where t.user_id=53

    union all

    select t.id, t.parent_id, t.user_id 
    from my_table t
    inner join cte c on (c.parent_id=t.id)
)
select t.* from cte t;

Item and parents

with cte as (
    select t.id, t.parent_id, t.user_id 
    from my_table t 
    where t.user_id=53

    union all

    select t.id, t.parent_id, t.user_id 
    from my_table t
    inner join cte c on (c.id=t.parent_id)
)
select t.* from cte t;

Thanks.

score 4 · Answer 1 · answered Mar 04 '13 at 14:17

4

It is very convenient that you have the sequences. The parents have a sequence matching an initial subset of the one you are looking for. The same is true for the children, but in reverse.

The following comes close to what you want:

select mt.*
from (select sequence from my_table where USER_ID = 53) theone join
     my_table mt
     on mt.sequence like theone.sequence+ '%' or
        theone.sequence like mt.sequence + '%'

However, you have to be careful with 10 matching 1, for instance. So, let's add an additional period where appropriate:

select mt.*
from (select sequence from my_table where USER_ID = 53) theone join
     my_table mt
     on mt.sequence like theone.sequence+ '.%' or
        theone.sequence like mt.sequence + '.%'

answered Mar 04 '13 at 14:17

Gordon Linoff

1,242,037
58
646
786

good solution, Gordon. You have just forgotten to add the row with user_id=53: "on mt.id=theone.id or mt.sequence like theone.sequence+ '.%' or theone.sequence like mt.sequence + '.%'" – Diogo Arenhart Mar 05 '13 at 20:49
thinking like you, we can also do it using exists: select mt.* from my_table mt where exists(select id from my_table theone where user_id=53 and (mt.id=theone.id or mt.sequence like theone.sequence+ '.%' or theone.sequence like mt.sequence + '.%')) – Diogo Arenhart Mar 05 '13 at 20:52
@DiogoArenhart . . . Yes, I think something like that would work as well. – Gordon Linoff Mar 05 '13 at 20:56

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

The problem here is, that you want a recursive SELECT. A DBMS is not made for such a query. But it's of course possible.

SELECT t1.* FROM Table1 t1 WHERE user_id = 53

UNION ALL

SELECT t2.* FROM Table1 t1, Table1 t2
WHERE
  (t1.id = t2.parent_id OR t1.parent_id = t2.id) AND t1.user_id = 53

This query would give you each 1st degree relative.

For a recursive SELECT, have a look here: Recursive select in SQL

Solution for your query here: http://sqlfiddle.com/#!6/e1542/10/0

with cte as (
    select t.id, t.parent_id, t.user_id 
    from Table1 t 
    where t.user_id=53
),

cte1 as (
    select t.id, t.parent_id, t.user_id 
    from cte t

    union all

    select t.id, t.parent_id, t.user_id 
    from Table1 t
    inner join cte1 c on (c.parent_id=t.id)
),

cte2 as (
    select t.id, t.parent_id, t.user_id 
    from cte t

    union all

    select t.id, t.parent_id, t.user_id 
    from Table1 t
    inner join cte2 c on (c.id=t.parent_id)
)

select t.* from cte1 t

UNION

select t.* from cte2 t

edited May 23 '17 at 12:16

Community

1
1

answered Mar 04 '13 at 12:26

Benjamin M

23,599
32
121
201

A CTE can be used as a recursive query. And the two queries Diogo is using **are** recursive. – Mar 04 '13 at 12:36
yeah it can, i know. But it's exhausting for the database ;) .. i also posted the solution with an sql fiddle. ... And if you had read the first 3 sentences of my answer, you would have seen the words `But it's of course possible.` – Benjamin M Mar 04 '13 at 12:37
But you also wrote: "*A DBMS is not made for such a query*" which is not true. And the first statement does not waht Diogo wants (sorry I can't revoke my downvote - I only get "is no locked in"). – Mar 04 '13 at 12:51
It definitely not made for this kind of operation. It can cause hundreds to thousands of single SELECT queries (each including a JOIN), if the tree hierarchy gets bigger. – Benjamin M Mar 04 '13 at 12:53
A recursive stored procedure will be less efficient. And it depends on the DBMS. Oracle's recursive processing is extremely fast. – Mar 04 '13 at 12:58
I've only worked with Postgres and MySQL so far ... and the question's author works with MS SQL Server. Maybe this is faster than MySQL, which definitely can't handle big recursions that well. – Benjamin M Mar 04 '13 at 13:00
MySQL can not do recursive queries at all. – Mar 04 '13 at 13:00
Sorry, I meant Postgres, which supports CTE :-) – Benjamin M Mar 04 '13 at 13:02
It really works, Benjamin. I'll use the solution from Gordon just because is smaller and I couldn't notice significant difference in performance. If I didn't have the sequence column I think this would be the only way to do it. Thank you. – Diogo Arenhart Mar 05 '13 at 21:02

vikram khambra · Answer 3 · 2022-01-06T07:54:10.450

0

declare @Rownumber int;
CREATE TABLE #GetSeqUser (
    RowNumber int,
    UserId int,
)
INSERT INTO #GetSeqUser Select ROW_NUMBER() OVER(ORDER BY EmpId ASC) AS ROWNUMBER, EmpId FROM  dbo.TreeSystem
set @Rownumber = (select ROWNUMBER from #GetSeqUser where UserId = 3 )
set @Rownumber = @Rownumber + 1
select UserId from #GetSeqUser where ROWNUMBER = @Rownumber

edited Jan 06 '22 at 07:54

answered Jan 06 '22 at 07:47

vikram khambra

1
1

Hi, thanks for the code snippet. It would be great if you could comment on how exactly it solved the OP's problem so we could all learn from it! – Simas Joneliunas Jan 09 '22 at 10:25

score 0 · Answer 4 · answered Jan 06 '22 at 07:51

0

declare @Rownumber int;
CREATE TABLE #GetSeqUser (
    RowNumber int,
    UserId int,
)
INSERT INTO #GetSeqUser Select ROW_NUMBER() OVER(ORDER BY EmpId ASC) AS ROWNUMBER, EmpId FROM  dbo.TreeSystem
set @Rownumber = (select ROWNUMBER from #GetSeqUser where UserId = 3 )
set @Rownumber = @Rownumber + 1
select UserId from #GetSeqUser where ROWNUMBER = @Rownumber

answered Jan 06 '22 at 07:51

vikram khambra

1
1

Hi, thanks for the code snippet. It would be great if you could comment on how exactly it solved the OP's problem so we could all learn from it! – Simas Joneliunas Jan 09 '22 at 10:27

How to select parents and children rows in SQL Server

4 Answers4