CODEWITHSUNDEEP
sql
Seanee
Seanee

Reputation: 3

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 !

Upvotes: 0

Views: 8071

Answers (4)

Sreenivas Pallavarapu
Sreenivas Pallavarapu

Reputation: 11

     Select 
     m.id,g.Name,m.Status,m.Name
     from
     table1 M,
     table2 G
     where
     m.Status >0  
     and g.Status = 0
     and m.Parent = g.id

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try like thsi

SELECT ID, 
       CASE WHEN STATUS = 0 THEN (SELECT Root WHERE Parent  = ID)
       END [Group]
       Status,
       Name
FROM TABLE1
WHERE STATUS <> 0

Upvotes: 1

Corrado Piola
Corrado Piola

Reputation: 869

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?

Upvotes: 0

wumpz
wumpz

Reputation: 9201

I think your main problem is to use the same table twice. This is achieved using different aliases like Corrado Piola explained it.

select t1.name, t2.name from mytab t1, mytab t2 where ...

Now t1 and t2 are acting like different tables.

Upvotes: 0

Related Questions