This discussion is archived
5 Replies Latest reply: Nov 26, 2012 12:12 AM by Manik RSS

a question about sql

969801 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

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