4 Replies Latest reply: Apr 1, 2013 6:31 AM by Saaz Ena RSS

    simple conplex join

    Saaz Ena
      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