CODEWITHSUNDEEP
mysqlsql
Aurora Bravo
Aurora Bravo

Reputation: 15

SQL: Insert columns from another table with matching object names

I have two tables:

Table1:
vm_name | host_name
         vm1 | server1
         vm2 | server1
         vm3 | server2

Table 2: object_id | object_name
                          1 | server1
                          2 | vm1
                          3 | vm3
                          4 | server2
                          5 | vm2

I want to add two columns into Table 1 (or make new entire table) that would display the vm_name, object_id, host_name, object_id. I also want to change the object_id column names to vm_id and host_id.

Output table would look like this:
vm_name | vm_id | host_name | host_id
         vm1 |    2     | server1       | 1
         vm2 |   5      | server1      | 1
         vm3 |    3     | server2      | 4

Any ideas on how to go about doing this? So far, I have I'm using join to match up: vm_name | object_id

SELECT table1.vm_name, table2.object_id
FROM table1
INNER JOIN table2
ON table1.vm_name = table2.object_name
ORDER BY table1.vm_name;

But how do I add both columns simultaneously?

Upvotes: 1

Views: 55

Answers (1)

sgeddes
sgeddes

Reputation: 62851

Join it back to table2 again:

select t1.vm_name, 
       t2.object_id as vm_id,
       t1.host_name,
       t3.object_id as host_id
from table1 t1
       join table2 t2 on t1.vm_name = t2.object_name
       join table2 t3 on t1.host_name = t3.object_name
order by t1.vm_name

Upvotes: 1

Related Questions