This discussion is archived
3 Replies Latest reply: Jan 17, 2013 10:30 PM by Most Wanted!!!! RSS

joining two tables

elmasduro Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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!!!! Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points