3 Replies Latest reply: Nov 21, 2012 10:31 PM by saranya.a RSS

    join query??

    saranya.a
      i have two tables....
      table 1
      origination_ip destination_ip
      1 4
      2 5
      3 6

      table2:
      ip endpoint
      1 a
      2 b
      3 c
      4 d
      5 e
      6 f
      i need the output as below:

      output table:
      orig_ip orig_endpoint dest_ip dest_endpoint

      1 a 4 d
      2 b 5 e
      3 c 6 f

      i tried with union, but union is not working in my db even for simple queries.. so i should go with other than union. can any one pls help???
        • 1. Re: join query??
          Frank Kulash
          Hi,

          Join two copies of table2, like 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 you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.

          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
          Added explanation
          • 2. Re: join query??
            user10857924
            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
            • 3. Re: join query??
              saranya.a
              Thank u so much.. got it