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
Thanks
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
Works fine , thanks !!!