Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: Select statement question

    I have the following SQL that returns the records as I expected:
    IF a.USERID=b.USERID AND b.CUSTID=c.CUSTID then return the records. Well what if I wanted to return all the records from
    TMOSER.CZCS_USERS a and TMOSER.CZCS_CUSTOMERS c even if there are no records in TMOSER.CZCS_PWR_USER_CUSTID b?

    SELECT a.USERID, c.CUSTID, b.DEFAULT_NPC, a.USER_NAME,
    a.FIRST_NAME, a.LAST_NAME, a.UPDATE_USERID, a.USER_EMAIL, c.CUSTOMER_NAME,
    c.LOGO_PATH, c.NPC, c.ISACTIVE
    FROM TMOSER.CZCS_USERS a, TMOSER.CZCS_PWR_USER_CUSTID b, TMOSER.CZCS_CUSTOMERS c
    WHERE a.USERID=b.USERID AND b.CUSTID=c.CUSTID

  2. #2
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8
    you could outer join the tables to obtain the desited result

    heres an example
    DEV> select * from tab1;

    ID COL1
    ---------- ----------
    1 A
    2 BC
    3 AB
    4 DE
    5 FAG

    Elapsed: 00:00:00.01

    DEV> select * from tab2;

    COL1
    ----------
    A
    G

    Elapsed: 00:00:00.00

    DEV> select tab2.* from tab2, tab1
    2 where tab2.col1 = tab1.col1;

    COL1
    ----------
    A

    Elapsed: 00:00:00.00

    the above retreived only matching rows but since you need all rows in TAB2 which do not match in TAB1...

    DEV> select tab2.* from tab2, tab1
    2 where tab2.col1 = tab1.col1(+);

    COL1
    ----------
    A
    G

    Elapsed: 00:00:00.00
    DEV>

  3. #3
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8
    if you need a 3 table example :-)

    DEV> select * from tab3;

    ID
    ----------
    1
    2
    7

    Elapsed: 00:00:00.00
    DEV> select * from tab1, tab2, tab3
    2 where tab1.id = tab3.id(+)
    3 and tab1.col1 = tab2.col1(+);

    ID COL1 COL1 ID
    ---------- ---------- ---------- ----------
    1 A A 1
    3 AB
    2 BC 2
    4 DE
    5 FAG

    Elapsed: 00:00:00.01
    DEV>

    so all records are from tab1 are displayed and corresponding tab2 and tab3 is displayed if matching else left blank

Posting Permissions

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