3 Replies Latest reply: Jan 18, 2013 12:30 AM by Most Wanted!!!! RSS

    joining two tables

    elmasduro
      consider the following data
      WITH data AS
      (
         SELECT 123 id, 'abc' txt, 'bc' txt2 FROM dual UNION all
         SELECT 111 id, 'abc1' txt, 'bc2' txt2 FROM dual UNION all
         SELECT 222 id, 'abc2' txt, 'bc3' txt2 FROM dual UNION all
         SELECT 333 id, 'abc3' txt, 'bc4' txt2 FROM dual UNION all
         SELECT 444 id, 'abc4' txt, 'bc5' txt2 FROM dual UNION all
         SELECT 555 id, 'abc5' txt, 'bc6' txt2 FROM dual 
      ) ,
      data2 AS (
         SELECT  'abc' txt, 1 cid FROM dual UNION all
         SELECT  'abc1' txt, 2 cid FROM dual UNION all
         SELECT  'bc2' txt, 3 cid FROM dual UNION all
         SELECT  'abc3' txt, 4 cid FROM dual UNION all
         SELECT 'bc5' txt, 5 cid FROM dual UNION all
         SELECT  'bc6' txt, 6 cid FROM dual 
      
      )
      i am trying to join txt column in data with txt in data2. if an entry that exist in data does not exist in data2, then i want to use txt2 as the join.
      for example,
                                               
      my output should be as follow:
      id          text     cid
      ======     =====     ====
      123         abc       1
      111         abc1      2
      222         null      null
      333         abc3      4
      444         bc5       5
      555         bc6       6
       
      as you can see i am trying to join txt in data with txt in data2. if i cannot find an entry then i use txt2 to join with txt from data2.
      if txt or txt2 do not find a match in data2 then null is display

      can someone help me write a query with the above output? thanks
        • 1. Re: joining two tables
          onedbguru
          No. There are any number of books and blogs and forums that can assist you in learning how to do joins. You have not shown us anything that you have tried. We are not here to do your job/homework for you. There may be some that will be willing to do your job for you... I am not.

          Instead of doing a view like this, why don't you actually create a data and data2 table and start testing it for yourself.

          http://www.lmgtfy.com/?q=oracle+join+examples
          • 2. Top-N Query
            Frank Kulash
            Hi,

            So you want to join the tables.
            Sometimes, a row in data won't match anything in data2 (e.g., data.id=222), but you want to include that row from data anyway. That means you'll need an outer join.
            Sometimes, a row in data will match 2 rows in data2 (e.g., data.id=111 matches both data2.txt='abc1' and 'bc2'). In this case you'll only want the row that matches on data.txt. In other words, some matches are better than others, and you only want to keep the best match for each row in data. That's a job for a Top-N Query , like this:
            WITH     got_m_num     AS
            (
                 SELECT     d.id
                 ,     d2.*
                 ,     DENSE_RANK () OVER ( PARTITION BY  d.id
                                           ORDER BY          CASE  d2.txt
                                                         WHEN  d.txt
                                                     THEN  'A'
                                                 WHEN  d.txt2
                                                     THEN  'B'
                                                         ELSE  'C'
                                                     END
                                   )     AS m_num
                 FROM         data     d
                 LEFT OUTER JOIN     data2     d2  ON  d2.txt  IN (d.txt, d.txt2)
            )
            SELECT       id
            ,       txt
            ,       cid
            FROM       got_m_num
            WHERE       m_num     = 1
            ORDER BY  id
            ;
            • 3. Re: joining two tables
              Most Wanted!!!!
              WITH DATA AS
                   (SELECT 123 ID, 'abc' txt, 'bc' txt2
                      FROM DUAL
                    UNION ALL
                    SELECT 111 ID, 'abc1' txt, 'bc2' txt2
                      FROM DUAL
                    UNION ALL
                    SELECT 222 ID, 'abc2' txt, 'bc3' txt2
                      FROM DUAL
                    UNION ALL
                    SELECT 333 ID, 'abc3' txt, 'bc4' txt2
                      FROM DUAL
                    UNION ALL
                    SELECT 444 ID, 'abc4' txt, 'bc5' txt2
                      FROM DUAL
                    UNION ALL
                    SELECT 555 ID, 'abc5' txt, 'bc6' txt2
                      FROM DUAL),
                   data2 AS
                   (SELECT 'abc' txt, 1 cid
                      FROM DUAL
                    UNION ALL
                    SELECT 'abc1' txt, 2 cid
                      FROM DUAL
                    UNION ALL
                    SELECT 'bc2' txt, 3 cid
                      FROM DUAL
                    UNION ALL
                    SELECT 'abc3' txt, 4 cid
                      FROM DUAL
                    UNION ALL
                    SELECT 'bc5' txt, 5 cid
                      FROM DUAL
                    UNION ALL
                    SELECT 'bc6' txt, 6 cid
                      FROM DUAL)
              SELECT DISTINCT ID, CASE
                                 WHEN (a.txt = b.txt)
                                    THEN a.txt
                                 ELSE a.txt2
                              END text,
                              CASE
                                 WHEN (a.txt = b.txt) OR (a.txt != b.txt)
                                    THEN b.cid
                                 ELSE NULL
                              END cid
                         FROM (SELECT ROWNUM r, ID, txt, txt2
                                 FROM DATA) a,
                              data2 b
                        WHERE r = cid;
              Regards,
              Friend

              Edited by: most wanted!!!! on Jan 17, 2013 10:27 PM