single sql statement help

+----+--------+-------开发者_高级运维-+
| id |  name  | parent |
+----+--------+--------+
| 1  | AA     | 0      |
| 2  | AB     | 1      |
+----+--------+--------+

How do I get the name of id 1 given that I have the id 2 in one sql statement? i.e. Rather than selecting the parent then doing another select to get the name from that id.

To clarify, I have the id of 2 and I need to get the name of its parent.

I'm sure this is very simple - I just can't work it out!

---

Something like this: (haven't tested obviously)

SELECT second.name 
FROM
TableName first Join TableName second
ON first.parent = second.id
where first.id = 1

---

SELECT
  T1.name Child, T2.name Parent
FROM 
  Table T1
LEFT JOIN 
  Table T2 ON T1.parent = T2.id
WHERE 
  T1.id = 1

---

If the hierarchy is always up to 1-level deep in your application, you may as well do:

SELECT `name`
FROM `TableA`
WHERE `id` = (SELECT `parent_id`
    FROM `TableA`
    WHERE `id` = 2
);

By one-level deep it means that relationships like 1 (is-parent-of) 2 (is-parent-of) 3 does not exist.

However if there can be N-level hierarchy, queries from Yochai Timmer and Parkyprg are just fine.