3 Replies Latest reply on Nov 22, 2012 4:31 AM by saranya.a

    join query??

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

      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

          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??
            Or may be like this...
                 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
                 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)
            group by origination_ip, destination_ip
            1 person found this helpful
            • 3. Re: join query??
              Thank u so much.. got it