This content has been marked as final. Show 3 replies
Join two copies of table2, like this:
If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.
SELECT t.origination_ip , o.endpoint , t.destination_ip , d.endpoint FROM table1 t JOIN table2 o ON o.ip = t.origination_ip JOIN table2 d ON d.ip = t.destination_ip ORDER BY t.origination_ip ;
If all of the originations were in one table called o, and all the destinations were in another table, called d, then you'd know how to join the 3 tables, right? It just so happens that o and d are really the same table, but that doesn't really change the query.
Edited by: Frank Kulash on Nov 21, 2012 10:43 AM
Or may be like this...
select max(case when rno=1 then ip END) as origination_ip, max(case when rno=1 then endpoint END) as orig_endpoint, max(case when rno=2 then ip END) as destination_ip, max(case when rno=2 then endpoint END) as dest_endpoint from ( select origination_ip, destination_ip, ip, endpoint, row_number()over(partition by origination_ip, destination_ip order by ip) as rno from t1 join t2 on t2.ip in (t1.origination_ip, t1.destination_ip) )x group by origination_ip, destination_ip