This content has been marked as final. Show 3 replies
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.
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 ;
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;
Edited by: most wanted!!!! on Jan 17, 2013 10:27 PM