How do I join names using the same table in SQL?

I have a table SiteUsers with SiteUserID, FirstName, LastName and SiteUserParentID.

Every SiteUser is assigned a SiteUserParentID which is referencing a SiteUserID.

I'd like to show the relationship with the full name of the SiteUser along with the full name of who they are assigned to.

What is the best way to do this?

    SiteUserID, FirstName, LastName ParentID
      1       , John     , Doe     , 1
      2       , Sallie   , Smith   , 1

I'm looking for the output to be:

ChildName   , ParentName
John Doe    , John Doe
Sallie Smith, John Doe

I of course want to dump this into an asp.net repeater and have the output be:

John Doe is assigned to John Doe
Sallie Smith is assigned to John Doe

Currently, I'm looking at:

John Doe is assigned to 1
Sallie Smith is assigned to 1

using the following:

    static public List<SiteUser> GetSiteUserListFullName()
{
    List<SiteUser> thelist = new List<SiteUser>();
    string sql = "select SiteUserID, SiteUserFirstName + ' ' + SiteUserLastName as SiteUserName, SiteUserParentID from SiteUsers where SiteUserActive = 1";
    SqlDataReader dr = DBUtil.FillDataReader(sql);

    while (dr.Read())
    {
        SiteUser obj = new SiteUser();

        obj.siteUserID = Convert.ToInt32(dr["siteUserID"].ToString());
        obj.siteUserFirstName = Convert.ToString(dr["siteUserName"].ToString());
        obj.siteUserParentID = Convert.ToInt32(dr["siteUserParentID"].ToString());
        thelist.Add(obj);
    }
    return thelist;
}

And this in my code behind:

    repeaterTeams.DataSource = SiteUser.GetSiteUserListFullName();
    repeaterTeams.DataBind();

And this on the front en开发者_C百科d:

<asp:Repeater ID="repeaterTeams" runat="server">
<HeaderTemplate>
    <table class="horTable">
    <tr class="tableHeader">
        <td class="first">Assignment</td>
        <td class="last">Edit</td>
    </tr>
</HeaderTemplate>
<ItemTemplate>
    <tr class="tableRow">
        <td><%#DataBinder.Eval(Container.DataItem, "SiteUserFirstName")%> is assigned to <%#DataBinder.Eval(Container.DataItem, "SiteUserParentID")%></td>
        <td class="last"><a href="selected-team.aspx?id=<%#DataBinder.Eval(Container.DataItem, "SiteUserID")%>">Select</a></td>
    </tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>

Any help is appreciated.

Thanks!

You need to join the table on itself.

Example:

select 
    child.SiteUserFirstName + ' ' + child.SiteUserLastName as ChildName,
    parent.SiteUserFirstName + ' ' + parent.SiteUserLastName as ParentName 
from 
    SiteUsers as child
inner join 
    SiteUsers as parent on child.SiteUserParentID = parent.SiteuserID
where 
    child.SiteUserActive = 1

You need to use a self join.

You can join the table to itself, so the sql would be:

select 
    a.SiteUserID, 
    a.SiteUserFirstName + ' ' + a.SiteUserLastName as SiteUserName,
    b.SiteUserFirstName + ' ' + b.SiteUserLastName as ParentName,
SiteUserParentID 
from SiteUsers a
JOIN SiteUsers b
on a.ParentID = b.SiteUserID
where SiteUserActive = 1