Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts

    Oracle 10g LEFT OUTER JOIN issue

    Hello, everyone.

    I am new to Oracle (I have mostly MS-SQL experience) and have an issue that has me stumped.

    LEFT OUTER JOIN is supposed to grab everything from the LEFT table that matches the WHERE clause, regardless of whether or not there is any corresponding data in the RIGHT table. I have a query that when run strictly on the LEFT table will produce 26 records; but when I include the code for the JOIN, I get nothing. Could someone please look at these and tell me what I am doing incorrectly?

    Here is the code that is just for the LEFT table:
    Code:
    SELECT a.edu_id, a.edu_title, a.edu_desc_tx, a.app_nm, a.edu_prereq
    FROM tableA a
    WHERE a.trn_app_id = 3
        AND a.edu_status = 'Y'
    ORDER BY a.edu_reorder_id
    This will return 26 records.

    If I make it with the LEFT OUTER JOIN, I get ZERO records:
    Code:
    SELECT a.edu_id, a.edu_title, a.edu_desc_tx, a.app_nm, a.edu_prereq,
        b.class_id, b.class_start_date, b.class_end_date
    FROM tableA a LEFT OUTER JOIN tableB b ON b.edu_id = a.edu_id
    WHERE a.trn_app_id = 3
        AND a.edu_status = 'Y'
        AND b.class_status = 'Y'
        AND b.class_start_date > sysdate
    ORDER BY a.edu_reorder_id, b.class_start_date
    Thank you,
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    All of the conditions on the second table (table b in this case) belong in the ON clause and not in the WHERE clause.

  • Users who have thanked guelphdad for this post:

    WolfShade (07-09-2012)

  • #3
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts

    Resolved

    I'm going to have a huge, red handprint on my forehead as soon as I'm done typing.

    Thank you, guelphdad. That was exactly my issue.
    Last edited by WolfShade; 07-09-2012 at 10:06 PM.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".


  •  

    Tags for this Thread

    Posting Permissions

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