self join

[bxgirl]

Here's my table

id, parent_id, description
--------------------------
1 0 ABC
2 0 DEF
3 0 GHI
4 1 JKL

When retrieving my records how can I design the sql code to retrieve the data like so:

ID description child_id description
------------------------------------
1 ABC 4 JKL


Thanks.

 

[bborissov]

SELECT MyTable.Id,
       MyTable.Description
       Tbl1.Id          AS Child_Id,
       Tbl1.Description AS ChildDesc
FROM MyTable
INNER JOIN MyTable Tbl1 ON MyTable.Id = Tbl1.Parent_Id
WHERE Parent_Id = 0

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bxgirl]

Thanks! I will try this.