CODEWITHSUNDEEP
mysqldatabasejoinself-join
Raf
Raf

Reputation: 708

MySql query : Show Columns as Rows from 1 table

Tables: Employee and Project

Employee:

EmployeeID|  Username | 
----------+-----------+
1         | Manager1  | 
2         | Manager2  | 
3         | Employee3 | 
4         | Employee4 | 
5         | Employee5 |

Project:

Note: Columns Project_ManagerID and EmployeeID comes from 1 table (Employee)

 ProjectID | Project_Name| Project_ManagerID | EmployeeID
-----------+-------------+-------------------+---------
1          | Project 1   | 1                 | 3
2          | Project 2   | 1                 | 4

Project table Explanation: For every Project, there are 2 Employee involve. Project Manager and a Regular Employee

The query results I'm looking for:

ProjectID  | EmployeeID | Username  |
-----------+------------+-----------+
1          | 1          | Manager1  |
1          | 3          | Employee3 |
2          | 1          | Manager1  |
2          | 4          | Employee4 |

I tried to make a self join but I'm getting difficulties in showing 2 columns in Employee table to display a single Column as Rows like my query result.

I always ended up 2 rows.. it should have 2 employee rows for every project. 

2 Projects * 2 Employees = 4 Rows in total

Upvotes: 0

Views: 727

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You can do union all something as

select
p.ProjectID,e.EmployeeID,e.Username 
from Project p 
join Employee e on e.EmployeeID = p.Project_ManagerID

union all

select
p.ProjectID,e.EmployeeID,e.Username 
from Project p 
join Employee e on e.EmployeeID = p.EmployeeID

Upvotes: 2

Related Questions