CODEWITHSUNDEEP
sql
bleeetiso
bleeetiso

Reputation: 21

How to create a table from different query results SQL

I want to create a new table using the results from some queries. I might be looking at this the wrong way so please feel free to let me know. Because of this I will try to make this question simple without putting my code to match each employee number with each manager level column from table2

I have two tables, one has employee names and employee numbers example

table 1

+-------------+-----------+-------------+-------------+
| emplpyeenum | firstname |   last name |    location |
+-------------+-----------+-------------+-------------+
| 11          |    joe    |    free     | JE          |
| 22          |   jill    |   yoyo      | XX          |
| 33          |    yoda   |   null      | 9U          |
+-------------+-----------+-------------+-------------+

and another table with employee numbers under each manager level so basically a hierarchy example

Table 2

+---------+----------+----------+
| manager | manager2 | manager3 |
+---------+----------+----------+
|      11 |       22 |       33 |
+---------+----------+----------+

I want to make a new table that will have the names besides the numbers, so for example but with employee number beside the names

+---------+--------+----------+
| level 1 | level2 |  level3  |
+---------+--------+----------+
| jill    |   joe  |    yoda  |
+---------+--------+----------+

How can I do this?

edit sorry guys I don't have permission to create a new table or view

Upvotes: 0

Views: 75

Answers (2)

Eric
Eric

Reputation: 3257

Why not change your table2 to this?

+------------+----------+
| EmployeeId | ManagerId|
+------------+----------+
|      11    |     NULL |
+------------+----------+
|      22    |     11   |
+------------+----------+
|      33    |     22   |
+------------+----------+

Then you can do what you want with the data. At least your data will be properly normalized. In your table2. What happen if employee 33 hire another employee below him? You will add another column?

Based on your available table, this should give you the result you want.

SELECT m1.firstname, m2.firstname, m3.firstname
FROM table2 t
LEFT JOIN table1 m1 ON m1.employeenum = t.manager
LEFT JOIN table1 m2 ON m2.employeenum = t.manager2
LEFT JOIN table1 m3 ON m3.employeenum = t.manager3

Upvotes: 1

B. Cratty
B. Cratty

Reputation: 1991

You can just do a basic create table, then do a insert select to that will fill the table the way you need it. All you have to do is replace the select statement that I provided with the one you used to create the levels table output.

create table Levels
(
level1 varchar(25),
level2 varchar(25),
level3 varchar(25)
)


insert into Levels(level1, level2, level3)
select * from tables --here you would put the select statement that you used to create the information. If you dont have this script then let me know

Upvotes: 0

Related Questions