Is it possible to select a value in the table on another row using that id?

I have a table which has the following structure:

id   开发者_开发问答  name    id_relation
---------------------------
1      this    NULL
2      that    1

I want a query to get, instead of id_relation, the name of the correlating id (in this case - 'this', so at the end, I'll get this result:

id  name    parent_name
-----------------------
2   that    this

Is it possible to do this?

---

Yes. Join the table to itself:

select t1.id, t1.name, t2.name as parent_name
from mytable t1
left join mytable t2 on t2.id = t1.id_relation
where t1.id = 2; -- where clause is optional. leave off to get all rows

This query will return rows for every row in your table, even if there isn't a matching "relation" row.

If you want to restrict the result rows to only those that have a matching row (as your example suggests), remove the LEFT keyword.

---

You have to do a table join on itself. Something like; select a.name, b.name from foo a, foo b where a.id_relation = b.id;