Self Join in same table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Self Join in same table

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-01-11 : 12:09:38

Hello !-

How do I achieve this?

Table A
Type    Child_id  Name         Parent_id
Parent  child_01  JohnDoe      child_02 
Child   child_02  PaulSmith    NULL     
Child   child_03  BrendaSmith  NULL     
Child   child_04  jasonSmith   NULL

The output should be (I'm only concerned where Type is Parent):

Parent_details Child Details
JohnDoe PaulSmith

Appreciate your help.

queenofcode
Starting Member

6 Posts

Posted - 2010-01-11 : 12:20:58

select parent.name, child.name
from tableA parent
join tableA child
on child.child_id = parent.parent_id
where parent.type = "Parent"

You know that the child's child_id is stored as the parent_id in the parent's listing, so if you join the two tables on the child's child_id = parent's parent_id, you should get the correct results.

<geek> The Self-Proclaimed Queen of Code :) </geek>

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-01-11 : 12:35:37

Thanks!. It worked. i couldn't figure out the join earlier.

Thanks again!!

queenofcode
Starting Member

6 Posts

Posted - 2010-01-11 : 13:15:37

No problem! Glad I could help :)

<geek> The Self-Proclaimed Queen of Code :) </geek>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources