Select statement help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Select statement help

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Select statement help

    I have a question:

    If I have two tables in Oracle, say T1 and T2, and they have the following properties:

    Code:
    T1
      c1  c2   c3
     ______________
    | A | 1 | alpha|
    | B | 2 | beta |
    | C | 3 | zeta |
    
    
    T2
      c1  c2  c4
     _____________
    | B | 2 | doh |
    Now my problem: I want to have a single select statement such that it returns 4 columns, namely T1.c1, T1.c2, T1.c3, T2.c4. It should return all the rows from T1.

    T2.c4 should return either null or its actual value from T2 if (T1.c1 = T2.c1 AND T1.c2 = T2.c2).


    So the sample output of this select statement would be:

    Code:
    Select ...
    
      c1  c2   c3    c4
     ____________________
    | A | 1 | alpha| null|
    | B | 2 | beta | doh |
    | C | 3 | zeta | null|
    
    
    Thanks in advance!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Code:
    SELECT t1.c1, t1.c2, t1.c3, NVL(t2.c4, 'null') c4
      FROM t1
      LEFT OUTER JOIN t2
        ON t1.c1 = t2.c1
       AND t1.c2 = t2.c2;
    Is this what your teacher wanted???
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Actually, it's for my work... but thanks though!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by gandolf989
    Code:
    SELECT t1.c1, t1.c2, t1.c3, NVL(t2.c4, 'null') c4
      FROM t1
      LEFT OUTER JOIN t2
        ON t1.c1 = t2.c1
       AND t1.c2 = t2.c2;
    Is this what your teacher wanted???
    LOL!!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width