SQL query to get results from duplicate rows

Question

I am really new in sql query and i have 1 question. Below are the details:

I have two tables:

user_table

user_id username    level
-------------------------
1       adam        0
2       david       1
3       danny       2
4       siva        0
5       muthu       0
6       chong       0

managing_level

staff   manager level
---------------------
1       2       1
1       3       2
4       3       2
5       2       2

I want to get all the username and their managers

expected result:

username    manager1   manager2
-------------------------------
adam        david      danny
siva                   danny
muthu       david

This is the query that I used:

 select u.username as username,
 (select username from user where user_id=m.manager and level=1) as manager1,
 (select username from user where user_id=m.manager and level=2) as manager2
 from user u, managing_level manager
 where u.user_id=m.staff;

result:

username    manager1   manager2
-------------------------------
adam        david      
siva                   danny
muthu       david

As you can see, it doesn't show the second manager for adam.

this may help https://stackoverflow.com/questions/31827817/sql-server-equivalent-of-oracle-connect-by-prior-and-order-siblings-by — Ab Bennett, Nov 09 '17 at 07:04

Radim Bača · Answer 1 · 2017-11-09T07:26:51.213

1

Use conditional aggregation.

select usr.username as username,
   max(case when man.level=1 then man.username end) as manager1,
   max(case when man.level=2 then man.username end) as manager2
from user_table usr
join managing_level m on m.staff = usr.user_id
join user_table man on m.manager = man.user_id
group by usr.user_id, usr.username

demo

One more detail: do not use the commas behind FROM. It considered to be a bad habit. Use JOIN instead.

edited Nov 09 '17 at 07:26

answered Nov 09 '17 at 07:17

Radim Bača

10,646
1
19
33

@StanislavL I was thinking about that, but then I considered to be not the case. – Radim Bača Nov 09 '17 at 07:19

score 1 · Answer 2 · answered Nov 09 '17 at 07:26

It's not clear what is level field in the managing_level table for but here is the query to get expected results:

select MAX(u0.username)  as username,
       MAX(u1.username)  as manager1,
       MAX(u2.username)  as manager2

from managing_level m
LEFT JOIN user_table u0 ON m.staff=u0.user_id
LEFT JOIN user_table u1 ON (m.manager=u1.user_id) AND (u1.level=1)
LEFT JOIN user_table u2 ON (m.manager=u2.user_id) AND (u2.level=2)
GROUP BY m.staff

SQLFiddle demo

nice solution without `CASE` – Radim Bača Nov 09 '17 at 07:36 — Radim Bača, Nov 09 '17 at 07:36

score 1 · Accepted Answer · answered Nov 09 '17 at 07:32

If level means the "number" of manager, try this:

SELECT u.username, m1.username AS manager1, m2.username AS manager2
FROM user_table u LEFT JOIN (SELECT m.staff, m.manager, m.level, s1u.username
                             FROM managing_level m INNER JOIN user_table s1u ON m.manager = s1u.user_id
                             WHERE m.level = 1
                            ) m1 ON u.user_id = m1.staff
                  LEFT JOIN (SELECT m.staff, m.manager, m.level, s2u.username
                             FROM managing_level m INNER JOIN user_table s2u ON m.manager = s2u.user_id
                             WHERE m.level = 2
                            ) m2 ON u.user_id = m2.staff
WHERE m1.staff IS NOT NULL OR m2.staff IS NOT NULL

SQL query to get results from duplicate rows

3 Answers3