CODEWITHSUNDEEP
sql
Deborah
Deborah

Reputation: 31

SQL nested query

I have a table like below

id  name     dependency
-----------------------
1   xxxx     0
2   yyyy     1
3   zzzz     2 
4   aaaaaa   0
5   bbbbbb   4
6   cccccc   5

the list goes on. I want to select group of rows from this table , by giving the name of 0 dependency in where clause of SQL and till it reaches a condition where there is no more dependency. (For ex. rows 1,2, 3 forms a group, and rows 4,5,6 is another group) .please help

Upvotes: 3

Views: 965

Answers (5)

Alexander Malakhov
Alexander Malakhov

Reputation: 3529

Hierarchical query will do:

SELECT * 
  FROM your_table
 START WITH id = :id_of_group_header_row
 CONNECT BY dependency = PRIOR id

Query works like this:

1. select all rows satisfying START WITH condition (this rows are roots now)
2. select all rows satisfying CONNECT BY condition,
keyword PRIOR means this column's value will be taken from the root row
3. consider rows selected on step 2 to be roots
4. go to step 2 until there are no more rows

Upvotes: 0

Thomas
Thomas

Reputation: 64674

Since you did not specify a product, I'll go with features available in the SQL specification. In this case, I'm using a common-table expression which are supported by many database products including SQL Server 2005+ and Oracle (but not MySQL):

With MyDependents As
    (
    Select id, name, 0 As level
    From MyTable
    Where dependency = 0
        And name = 'some value'
    Union All
    Select T.id, T.name, T.Level + 1
    From MyDependents As D
        Join MyTable As T
            On T.id = D.dependency
    )
Select id, name, level
From MyDependents

Another solution which does not rely on common-table expressions but does assume a maximum level of depth (in this case two levels below level 0) would something like

Select T1.id, T1.name, 0 As level
From MyTable As T1
Where T1.name = 'some value'
Union All
Select T2.id, T2.name, 1 
From MyTable As T1
    Join MyTable As T2
        On T2.Id = T1.Dependency
Where T1.name = 'some value'
Union All
Select T3.id, T3.name, 2
From MyTable As T1
    Join MyTable As T2
        On T2.Id = T1.Dependency
    Join MyTable As T3
        On T3.Id = T2.Dependency
Where T1.name = 'some value'

Upvotes: 1

dkretz
dkretz

Reputation: 37655

If you can estimate a max depth, this works out to something like:

SELECT 
   COALESCE(t4.field1, t3.field1, t2.field1, t1.field1, t.field1),
   COALESCE(t4.field2, t3.field2, t2.field2, t1.field2, t.field2),
   COALESCE(t4.field3, t3.field3, t2.field3, t1.field3, t.field3),
....
FROM table AS t
LEFT JOIN table AS t1 ON t.dependency = t1.id
LEFT JOIN table AS t2 ON t1.dependency = t2.id
LEFT JOIN table AS t3 ON t2.dependency = t3.id
LEFT JOIN table AS t4 ON t3.dependency = t4.id
....

This is a wild guess just to be different, but I think it's kind of pretty, anyway. And it's at least as portable as any of the others. But I don't want to look to closely; I'd want to use sensible data, start testing, and check for sensible results.

Upvotes: 0

jon_darkstar
jon_darkstar

Reputation: 16788

This is the first thing that came to mind. It can be probably done more directly/succinctly, I'll try to dwell on it a little.

SELECT *
FROM table T1
WHERE T1.id >= 
         (SELECT T2.id FROM table T2 WHERE T2.name = '---NAME HERE---')
      AND T1.id <
        (SELECT MIN(id)
        FROM table T3
        WHERE T3.dependency = 0 AND T3.id > T2.id)

Upvotes: 0

Dan
Dan

Reputation: 1509

Sounds like you want to recursively query your table, for which you will need a Common Table Expression (CTE)

This MSDN article explains CTEs very well. They are confusing at first but surprisingly easy to implement.

BTW this is obviously only for SQL Server, I'm not sure how you'd achieve that in MySQL.

Upvotes: 0

Related Questions