CODEWITHSUNDEEP
mysqlsql
Damien
Damien

Reputation: 95

SQL query to get results from duplicate rows

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.

Upvotes: 3

Views: 89

Answers (3)

quorti
quorti

Reputation: 320

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

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10711

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.

Upvotes: 1

valex
valex

Reputation: 24144

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

Upvotes: 1

Related Questions