Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    1

    Question Unanswered: Any outer join pros out there??

    Hello SQL experts and SQL-Join professionals ,

    I'm a little perplexed.

    When I run the following query, the first 10 records return what I want which is all the records where the columns service and system are NULL. (SEE EXHIBIT A) But I only want the first 10 records....

    However, when I add " and b.customer_no is NULL" I get a totally different result set. (SEE EXHIBIT B) Can someone explain what I'm doing wrong and how to fix this so that I only get records where system and service are null?

    Thanks so much!

    Jeff G.

    EXHIBIT A:

    select a.systemid, b.customer_no, a.service, b.service
    from tdocumentdetail a, tarmscontract b
    where a.systemid *= b.customer_no
    and a.service *= b.service
    and a.elementid = 3635
    order by b.customer_no

    RESULT SET FOR A:
    ----------- ----------- ------- -------
    8300673 NULL SMC NULL
    8600139 NULL TMC NULL
    8601174 NULL TMC NULL
    10007811 NULL TMC NULL
    10007812 NULL TMC NULL
    10007834 NULL TMC NULL
    10007852 NULL TMC NULL
    10007862 NULL TMC NULL
    29603389 NULL SH2 NULL
    29603389 NULL SMC NULL
    7003323 7003323 SMC SMC
    7003323 7003323 SMC SMC
    7003430 7003430 SMC SMC
    7003430 7003430 SMC SMC
    7003655 7003655 SHO SHO
    7003655 7003655 SHO SHO
    7003935 7003935 SHO SHO
    7003935 7003935 SHO SHO
    7003935 7003935 SHO SHO
    7003935 7003935 SHO SHO
    (this goes on and on....)

    EXHIBIT B:
    select a.systemid, b.customer_no, a.service, b.service
    from tdocumentdetail a, tarmscontract b
    where a.systemid *= b.customer_no
    and a.service *= b.service
    and a.elementid = 3635
    and b.customer_no is null (this is the part that changed -- i added this)
    order by b.customer_no

    RESULT SET FROM B:

    systemid customer_no service service
    ----------- ----------- ------- -------
    7003323 NULL SMC NULL
    7003430 NULL SMC NULL
    7003655 NULL SHO NULL
    7003935 NULL SHO NULL
    7004030 NULL EXT NULL
    7004030 NULL SH2 NULL
    7004030 NULL SH3 NULL
    7004030 NULL SMC NULL
    7004030 NULL TM2 NULL
    7004034 NULL EXT NULL
    7004034 NULL SH2 NULL
    7004034 NULL SH3 NULL
    7004034 NULL SMC NULL
    7004034 NULL TM2 NULL
    This goes on and on....Notice that it's totally different with that additional predicate.

    Does anyone have any suggestions??

    Thanks so much!

    Jeff


  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    order by a.systemid in both queries and you will see that exhibit B is just a subset of exhibit A

    you're not seeing it now because all the b.customer_no null values float to the top

    rudy
    http://rudy.ca/

Posting Permissions

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