CODEWITHSUNDEEP
sqlsql-serverselect
Czapa
Czapa

Reputation: 73

Selecting same column twice from a single table but with different conditions

I would like to display names and numbers of employees with numbers and names of their bosses, like below:

output

There is only one table:

table

I tried this so far:

SELECT  
    ID, 
    Name, 
    Boss, 
    (SELECT Name FROM Employees WHERE ID IN (SELECT Boss FROM Employees))
FROM Employees

But it gives me an error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

Upvotes: 5

Views: 44390

Answers (4)

APH
APH

Reputation: 4154

You need a self-join; something like:

Select a.ID, a.Name, b.ID as Boss, b.Name as BossName
from Employees A
left join Employees B
on a.Boss = b.ID

Upvotes: 13

Ulysses Koudou
Ulysses Koudou

Reputation: 51

I actually had to create a similar split in my Azure DB. Separating genders (men & Women) using their title.

enter image description here

Here is the bit of code.

select 
    (Select count(b.TITLE) from SalesLT.Customer b Where Title LIKE 'Mr%') as women 
    ,(Select count(c.TITLE) from SalesLT.Customer c Where Title LIKE 'Ms%') as men
    ,(Select count(d.TITLE) from SalesLT.Customer d Where Title NOT LIKE 'Ms%' AND Title NOT LIKE 'Mr%' ) as unidentified 
    ,count(a.TITLE) as Total
from SalesLT.Customer a

Result: enter image description here

Upvotes: 2

M.Doerner
M.Doerner

Reputation: 722

APH's answer is definitely the most effective solution. However, I would like to add a solution with a correlated subquery to show that you have been quite close to a working query. 

SELECT
    E.ID, 
    E.Name, 
    E.Boss,
    (SELECT Name FROM Employees WHERE ID = E.Boss) AS BossName
 FROM Employees E

The problem with your original query was that the condition in the subquery did not depend on the specific record. The subquery returned the name of every employee who is the boss of someone else.

Please note that this solution usually has a worse performance than the join because it has to execute the subquery for every item. Although the result is equivalent to that of the join many optimizers do not realize this because they do not touch subqueries.

Upvotes: 1

dana
dana

Reputation: 18125

One way to do this is to limit the sub-query to return the first result only using a TOP clause. You need to join it to the main table as well:

SELECT
    e.ID, 
    e.Name, 
    e.Boss, 
    (SELECT top 1 Name FROM Employees b where b.ID = e.Boss) as BossName
FROM Employees e

Upvotes: 4

Related Questions