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 t1.name, t2.name as "Group" from tab t1 left join tab t2 on t1.parent = t2.id where t1.status > 0;
If Parent ID is always present, you can use an inner join:
select t1.name, t2.name as "Group" from tab t1, tab t2 where t1.parent = t2.id and t1.status > 0;
Otherwise, please can you explain what you need?