5 Replies Latest reply: Nov 26, 2012 2:12 AM by Manik RSS

    a question about sql

    969801
      dear guru:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      CREATE TABLE E (rID NUMBER,sid number);
      
      
      
      create table f (id number,name varchar2(20));
      
      INSERT INTO E VALUES(3,4);
      INSERT INTO E VALUES(1,2);
      INSERT INTO E VALUES(4,5);
      INSERT INTO E VALUES(4,6);
      INSERT INTO E VALUES(5,6);
      
      
      INSERT INTO F VALUES(1,'a');
      INSERT INTO F VALUES(4,'b');
      INSERT INTO F VALUES(3,'c');
      
      
      SELECT RID,F.NAME rname,SID,G.NAME sname FROM E,F,F G 
      where e.rid=f.id(+) and e.sid=g.id(+);
      
      
      3     c     4     b
      5     null     6     null
      4     b     6     null
      4     b     5     null
      1     a     2     null
      
      
      
      is there any way to rewrite the sql ?    null ----rows is required..
      best regards;

      Edited by: 966798 on 2012-11-25 下午10:33
        • 1. Re: a question about sql
          Manik
          With your data, other way of writing it would be :
          SELECT RID,
                 F.NAME rname,
                 SID,
                 F.NAME sname
            FROM E, F
           WHERE e.rid = f.id(+);
          Cheers,
          Manik.
          • 2. Re: a question about sql
            969801
            thk for your reply
            i have made a mistake,

            the sql is like this 
            
            
            
            SELECT RID,F.NAME rname,SID,G.NAME sname FROM E,F,F G 
            WHERE E.RID=F.ID(+) AND E.sID=G.ID(+);
            
            
            3     c     4     b
            5     null     6     null
            4     b     6     null
            4     b     5     null
            1     a     2     null
            Edited by: 966798 on 2012-11-25 下午10:35
            • 3. Re: a question about sql
              793996
              Try with full outer join.

              SELECT *
              FROM E FULL OUTER JOIN F
              ON (RID = ID);

              Thanks,
              Vivek
              • 4. Re: a question about sql
                ranit B
                Try this...
                /* Formatted on 11/26/2012 12:08:33 PM (QP5 v5.163.1008.3004) */
                SELECT rid,
                         NVL(f.name,'Null') rname,
                         sid,
                         NVL((SELECT name FROM f WHERE id = sid),'Null') sname
                    FROM e FULL OUTER JOIN f ON e.rid = f.id
                ORDER BY rid;
                gives
                o/p = 
                1     a     2     Null
                3     c     4     b
                4     b     5     Null
                4     b     6     Null
                5     Null     6     Null
                HTH
                Ranit B.

                Edited by: ranit B on Nov 26, 2012 12:20 PM
                -- o/p added
                • 5. Re: a question about sql
                  Manik
                  Left outer join would also work
                  Also, IF it is just the matter of displaying NULL as NULL only specific to session, you may use SQL*PLUS as below:
                  set null "NULL"
                  
                    SELECT RID,
                           F.NAME rname,
                           SID,
                           (SELECT name
                              FROM f
                             WHERE id = sid)
                              sname
                      FROM E, F
                     WHERE E.RID = F.ID(+)
                  ORDER BY 1;
                  output
                         RID RNAME                       SID             SNAME
                  ---------- -------------------- ---------- --------------------
                           1 a                             2         NULL
                           3 c                             4         b
                           4 b                             6         NULL
                           4 b                             5         NULL
                           5 NULL                          6         NULL
                  Cheers,
                  Manik.