sql query for displaying a new column based on condition on existing columns-CodePudding

I am new to sql. I have a problem where I have to display an additional column based on some condition on existing columns. Existing table:

--------------------
 pid | ppid | path |
--------------------
111  | 120  | xyz  |
120  | 230  | abc  |

Where 'pid' -> processid , 'ppid' -> parent process id , 'path' -> path of process pid

I want to write a SQL query to display all these rows along with a new column 'ppath' which denotes the path of parent process. And this info can be retrieved from existing table itself. But I am not sure how to do this. Eg output:

--------------------------
 pid | ppid | path | ppath|
--------------------------
111  | 120  | xyz  | abc  |
120  | 230  | abc  |      |

Any suggestions would be helpful.

CodePudding user response：

You can (left) join the table to itself. Let's say your table is called process:

SELECT
   process.pid, 
   process.ppid, 
   process.path, 
   parent_process.path as ppath
FROM process
LEFT JOIN process as parent_process
ON process.ppid = parent_process.pid

CodePudding user response：

Its coding might be different based on the backend used but you didn't tag what you are using. For a general SQL:

select t1.*, t2.path as ppath
from myTable t1
left join myTable t2 on t1.ppid = t2.pid;