Need a mysql query

Question

I have a table named 'role'

id | parent_id | name
---+-----------+--------------------
1  | NULL      | Machine
2  | 3         | Ram
3  | 4         | Cpu
4  | NULL      | Computer
5  | NULL      | Electronic Device

I need a (mysql or postgress sql) query which will give the possible parents of a particular role.

For example

the possible parent roles of 'Computer' are

id | name
---+-------------------
5  | Electronic Device
1  | Machine

please help me

Answer 1

Use:

SELECT t.id,
       t.name
  FROM ROLES t
 WHERE t.parent_id IS NULL
   AND t.name != ?

...where ? is whatever name you want to exclude if it's parent_id is null.

Answer 2

Ignoring the fact that the data in your given example doesn't match up, I think what you're trying to do is store and fetch hierarchical data in a database, where you end up with a chain of parents and children.

There are several approaches to storing and retrieving this kind of data. I highly recommend you read this Sitepoint article: Storing Hierarchical Data in a Database.

The most common approach is the Adjacency List model, whereby you select the first record, then select it's parent, then select it's parent, etc, until you have the whole chain of records. This is a read-heavy, write-light approach, and easy to implement.

If you want a fast read approach, the Modified Preorder Tree Traversal (page 2) is an amazing algorithm. More difficult to implement, but you can select an entire set of children/parent records in a single SELECT.

Answer 3

Based on comments from other users (and maybe a lack of complete data), it seems like a self reference table. So if the data is corrected you can try

select r2.* 
from role as r1 inner join 
role as r2 on r1.parent_id=r2.id 
where r1.name='Computer';

with data being

id | parent_id | name
---+-----------+--------------------
1  | NULL      | Machine
2  | 3         | Ram
3  | 4         | Cpu
4  | 1         | Computer
5  | NULL      | Electronic Device