CODEWITHSUNDEEP
mysqlsql
Itai Sagi
Itai Sagi

Reputation: 5615

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?

Upvotes: 1

Views: 66

Answers (2)

Bohemian
Bohemian

Reputation: 425208

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.

Upvotes: 3

John
John

Reputation: 721

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;

Upvotes: 0

Related Questions