Warm tip: This article is reproduced from serverfault.com, please click

Combine two table in a new one

发布于 2020-11-29 11:45:22

I have two tables in a Postgres , table 1 with hosts, ips and severity, and table 2 with geo localization of each networks. I need to create a table 3 with all data of the table 1 and comparing the host (table 1) with network from table 2 to add on table 1 de column site, how can I do this.

TABLE 1     
HOST        |     IP            | SEVERITY
HOST - 1    | 172.2.36.2        |   7
HOST - 2    | 172.2.34.35       |   7
HOST - 3    | 172.2.36.9        |   8
HOST - 4    | 172.2.34.77       |   6
HOST - 5    | 172.2.36.2        |   5
HOST - 6    | 172.2.36.99       |   3
HOST - 7    | 172.2.34.55       |   4


TABLE 2 
NETWORK     | SITE
172.2.36.   | SITE A
172.2.34.   | SITE B


TABLE 3     (NEW)   
HOST        | IP            | SEVERITY  |   SITE
HOST - 1    | 172.2.36.2    |   7       |  SITE A
HOST - 2    | 172.2.34.35   |   7       |  SITE B
HOST - 3    | 172.2.36.9    |   8       |  SITE A
HOST - 4    | 172.2.34.77   |   6       |  SITE B

Table 1

Table 2

Table Result

Thanks

Questioner
luiz espinola
Viewed
0
forpas 2020-11-30 01:15:36

You need a join of Table1 to Table2:

select t1.*, t2.site
from Table1 t1 inner join table2 t2
on t1.ip like concat(t2.network, '%')
where t1.severity >= 6