CODEWITHSUNDEEP
sqlsql-serversubqueryself-join
Florian
Florian

Reputation: 4728

SQL handle data in table - which techniques to use?

I want to handle data in ONE table this is the table like this:

here is my table:

ID  Name    Role    WID
-----------------------
1   Flo     EMP     888
2   Jack    MIDMAN  888
3   Bison   BOSS    888

And I want to get only one row and add the boss information like this:

ID  Name    Boss        WID 
---------------------------
1   Flo     Bison       888

I did this query:

select
    ID,
    Name,
    (SELECT Name FROM Employee WHERE Role = 'BOSS' and  WID = 888) AS Boss,
    WID
    from Employee
where WID = 888
and Role <> 'BOSS' and Role <> 'MIDMAN'

Is there another better/cleaner way to do this ? I tried with self-join but not successfully...

Upvotes: 1

Views: 60

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37430

Try this:

select [EMP].ID,
       [EMP].Name [Name],
       [BOSS].Name [Boss],
       [EMP].WID
from (
    select Name, 'EMP', WID from MY_TABLE where Role = 'EMP'
) as [EMP] join (
    select Name, 'BOSS', WID from MY_TABLE where Role = 'BOSS'
) as [BOSS] on [EMP].WID = [BOSS].WID

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a join as 

select a.Name. b.Name as Boss, a.WID
from Employee a 
inner join Employee b on and a.WID = 888 and a.WID = b.WID 
      and b.role ='BOSS' and a.Role NOT IN (BOSS', 'MIDMAN')

Upvotes: 1

Abhishek
Abhishek

Reputation: 2490

A self-join example -

select e1.id,e1.name,e2.name as boss,e1.wid
from Employee e1 inner join Employee e2
on e1.wid = e2.wid
and e1.role = 'EMP' and e2.role = 'BOSS'

Upvotes: 1

Related Questions