user813720
user813720

Reputation: 451

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!

Upvotes: 2

Views: 58

Answers (3)

Abhay
Abhay

Reputation: 6645

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.

Upvotes: 0

CristiC
CristiC

Reputation: 22698

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

Upvotes: 0

Yochai Timmer
Yochai Timmer

Reputation: 49251

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

Upvotes: 2

Related Questions