Seanee Seanee - 7 months ago 32
SQL Question

Creating a SQL column based on the value of another column in the same table

I must not be using the right Google foo to find the correct answer.

I have a table that looks like the following:

ID Parent  Status   Name

1              NULL     0              Root 2             1              0              Group 1

3             6              400         WINXP32

4              2              400         WIN7

5              2              400         WIN2K8

6              1              0              Group 2

The 'name' column contains both group names and machine names. Groups have a 'Status' of 0.
How would I create a SQL statement to display (not modify the database) a column called 'Group' based on the logic that the 'Parent' column holds the 'ID' of its parent.

Somehow I can figure out how to join data from other tables but I cant figure this out...

-- Update: I forgot to show an example of the output I'm looking for, in a perfect world I would get the following..

ID | Group | Status | Name

3 | Group 2 | 400 | WINXP32

4 | Group 1 | 400 | WIN7

5 | Group 1 | 400 | WIN2K8

I would (i think) filter out the groups from the output by doing a where status > 0

Thanks everyone for the quick feedback !


Do you mean something like this (Self join in SQL standard)?

If it's possible that you have records with missing Parent ID:

select, as "Group"
from tab t1
     left join tab t2
     on t1.parent =
where t1.status > 0;

If Parent ID is always present, you can use an inner join:

select, as "Group"
from tab t1, tab t2
where t1.parent =
  and t1.status > 0;

Otherwise, please can you explain what you need?