CODEWITHSUNDEEP
sqlsql-server
bagusofterror
bagusofterror

Reputation: 75

SQL- How to select all dynamic parent and child data in 1 query

I want to select all data that have parent and child. I have a sample table like this:

ID  Name        Parent  
1   Mike        6000    
2   Mike_x1     1
3   Mike_x2     2
4   Mike_x3     6333
5   Mike_x4     2
6   Mike_x5     3
7   Bob_x2      5

first of all i select data to get a header:

select * from table1 where parent = 1

and i get header data like this:

ID  Name        Parent
2   Mike_x1     1

from the header i got a new ID = 2 as a header data. So i select again to get a child:

select * from table1 where parent = 2

and i get data like this:

ID  Name        Parent
3   Mike_x2     2
5   Mike_x4     2

from the first child i got a new ID = 3 and ID = 5. So i select again to get another child:

select * from table1 where parrent = 3

and

select * from table1 where parrent = 5

and i got data like this:

ID  Name        Parrent
6   Mike_x5     3

and 

ID  Name        Parrent
7   Bob_x2      5

Thats my steps to get parent and child. But i want to select using 1 query to get all parent and child data. When i do first select to get header select * from table1 where parrent = 1 , i will get a result like this:

ID  Name        Parrent
2   Mike_x1     1
3   Mike_x2     2
5   Mike_x4     2
6   Mike_x5     3
7   Bob_x2      5

Anybody help me? Is it possible for me to do it by 1 query, which is don't need to select manual like select * from where parent = 1,2,3,5 etc

Upvotes: 0

Views: 1631

Answers (2)

xQbert
xQbert

Reputation: 35323

Assuming my understanding of the question is correct, this will " select all data that have parent and child"

Select T1.*
FROM TableName T1
INNER JOIN tableName Parents
 on T1.ParentID = Parents.ID
INNER JOIN tableName Kids
 on Kids.ParentID = T1.ID

corrected inner join on last element Kids.ID should have been Kids.ParentID

Should result in

    ID  Name        Parent  
    2   Mike_x1     1 
    3   Mike_x2     2 
    5   Mike_x4     2

This works because of the inner self joins which exclude records that don't have parents or kids.

If these aren't the desired results: what are the expected results?

If you need to know who the parents are and who the kids are simply add to the select

Select t1.*, Parents.*, Kids.*

However if you need to traverse the hieracy and list the parents kids of kids etc... then XML path or a cte is needed.

Upvotes: 0

ttaaoossuu
ttaaoossuu

Reputation: 7884

SELECT DISTINCT * FROM table1
WHERE ID IN (SELECT Parent FROM table1)
AND Parent IN (SELECT ID FROM table1)
ORDER BY ID ASC

Upvotes: 1

Related Questions