This discussion is archived
4 Replies Latest reply: Apr 1, 2013 4:31 AM by Saaz Ena RSS

simple conplex join

Saaz Ena Newbie
Currently Being Moderated
Hi all,

I am trying to make a simple complex join using oracle 10g, but unfortunately fails.
 
SQL> desc prod_m
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
ID       NUMBER(10)   Y                         
PRD_CODE VARCHAR2(10) Y                         
 
SQL> desc prod_c
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
PRD_CODE VARCHAR2(10) Y                         
POINT    NUMBER(10)   Y                         
TYPE     CHAR(1)      Y                         
 
I have two tables with below data.

SQL> select * from prod_m;
 
         ID PRD_CODE
----------- ----------------------------------------
          1 67ab
          1 68ab
          2 40ab
          2 45ab
          3 70cx
          3 71cx
 
6 rows selected
 
SQL> select * from prod_c;
 
PRD_CODE                                       POINT TYPE
---------------------------------------- ----------- ----
67ab                                              99 M
67ab                                              98 L
68ab                                              57 M
68ab                                              98 L
40ab                                              88 M
40ab                                              69 L
45ab                                              88 M
45ab                                              62 L
70cx                                              22 L
70cx                                              28 M
71cx                                              26 M
71cx                                              22 L
 
12 rows selected
 
The output should look as below
ID          PRD_CODE                          TYPE
----------   ---------------------------------------- ----
          1 67ab                                     L
          1 68ab                                     L
          2 40ab                                     M
          2 45ab                                     M
          3 70cx                                     L
          3 71cx                                     L
 
6 rows selected
 
Point column is the common column where prd code is related and we get type
for eg in id 1 the prd_c table has 98 point as common (matching) and hence we have type as L in output.


Could some one please help me.

Thanks
saaz

Legend

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