Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    27

    Unanswered: small join issue

    Greetings all,

    I have this query below that returns 1020 records below:

    select count (*) from CUSTOMER AS c join CUSTOMER_REMARKS AS cr on c.CUSTOMER_ID = cr.CUSTOMER_ID
    where (CUSTOMER_POSITION is NULL or CUSTOMER_POSITION != 'INACTIVE STATUS')
    and CUSTOMER_HEIRARCHY IN (select CUSTOMER_ID from CUSTOMER where CUSTOMER_HEIRARCHY = 1098895)
    and CUSTOMER_VIP = 'Y' and cr.REMARK_CODE = 'CN'

    When, I execute the non-join query below, I get 1027 records:

    select count (*) from CUSTOMER
    where (CUSTOMER_POSITION is NULL or CUSTOMER_POSITION != 'INACTIVE STATUS')
    and CUSTOMER_HEIRARCHY IN (select CUSTOMER_ID from CUSTOMER where CUSTOMER_HEIRARCHY = 1098895)
    and CUSTOMER_VIP = 'Y'

    I am assuming that there are 7 ID's in the CUSTOMER table, that are not in the CUSTOMER_REMARKS table. I have tried every combination of left & right joins, but I still get the 1020 record count. I thought using one of these combinations, I will get the NULL records. Am I missing something? How can I get the 1027 record count using the join? Any help would be appreciated?

    Sincerely,

    Patrick Quinn
    TQ3 Navigant Database Administrator

  2. #2
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43
    there is no reason why with an outer join your request doesn't send the 1027 records...
    and what about the clause "cr.REMARK_CODE = 'CN'" ?

Posting Permissions

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