Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Unanswered: Left outer join problem

    I have the following query:

    select co.contact_id, mobilephone.PHONENR_ID, mobilephone.PHONETYPE_ENUMID
    from contact co ,
    contactphonenr mobilecoph left join phonenr mobilephone on
    mobilephone.phonenr_id = mobilecoph.phonenr_id and mobilephone.phonetype_enumid = 4
    where co.CONTACT_ID = 200057 and co.CONTACT_ID = mobilecoph.contact_id

    which applies to three tables: contact, phonenr and contactphonenr which is a link table between contact
    and phonenr. The phonenr can be of three types: mobile, fax or main. I want the contact with the mobile
    phonenr phoneid or if not present with an empty phoneid.

    This query works more or less, but if the contact has a fax and main phonenr, but no mobile phonenr
    the result is two records without a phoneid in stead of one.
    Something simular happens when I try to select the fax for this contact, I receive a record with the fax and
    an empty record.

    Can anyone help me?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this (untested) --
    Code:
    select co.contact_id
         , mobilephone.PHONENR_ID
         , mobilephone.PHONETYPE_ENUMID 
      from contact co 
    inner
      join contactphonenr mobilecoph 
        on co.CONTACT_ID = mobilecoph.contact_id
    left outer
      join phonenr mobilephone 
        on mobilecoph.phonenr_id
         = mobilephone.phonenr_id 
       and mobilephone.phonetype_enumid = 4
     where co.CONTACT_ID = 200057  
    group
        by co.contact_id
         , mobilephone.PHONENR_ID
         , mobilephone.PHONETYPE_ENUMID  
    having count(distinct mobilephone.PHONENR_ID) <= 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    7

    work +-

    Hi,

    This query solves the first problem (thanks for that)
    =>but if the contact has a fax and main phonenr, but no mobile phonenr
    the result is two records without a phoneid in stead of one

    but not the second
    =>when I try to select the fax for this contact, I receive a record with the fax and an empty record

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, i'm beginning to see how insidious this problem really is

    what you should do is set up some test data so that you have

    - a user with no phones
    - a user with only a main phone
    - a user with only a fax phone
    - a user with only a mobile phone
    - a user with 2 main phones only
    - a user with a main and a fax only
    - a user with 2 mobiles only
    - a user with 1 of each
    - a user with 1 main phone and 2 mobiles

    then if you need help testing, dump the data so that we can create the same test suite

    oh, and what was your exact question again please?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    7

    create and insert statement to similate situation

    These are the statement to simulate the situation:

    CREATE TABLE CONTACT
    (
    CONTACT_ID NUMBER(12) NOT NULL
    );

    CREATE TABLE CONTACTPHONENR
    (
    PHONENR_ID NUMBER(12) NOT NULL,
    CONTACT_ID NUMBER(12) NOT NULL
    );

    CREATE TABLE PHONENR
    (
    PHONENR_ID NUMBER(12) NOT NULL,
    PHONETYPE_ENUMID NUMBER(12) NOT NULL,
    ZONALNR VARCHAR2(10 BYTE) NOT NULL,
    LOCALNR VARCHAR2(50 BYTE) NOT NULL
    );


    INSERT INTO CONTACT ( CONTACT_ID ) VALUES (200057);
    INSERT INTO CONTACT ( CONTACT_ID ) VALUES (200058);
    INSERT INTO CONTACT ( CONTACT_ID ) VALUES (200059);
    INSERT INTO CONTACT ( CONTACT_ID ) VALUES (200060);
    COMMIT;

    INSERT INTO CONTACTPHONENR (PHONENR_ID, CONTACT_ID ) VALUES (200061, 200057);
    INSERT INTO CONTACTPHONENR (PHONENR_ID, CONTACT_ID ) VALUES (200062, 200057);
    INSERT INTO CONTACTPHONENR (PHONENR_ID, CONTACT_ID ) VALUES (200063, 200058);
    INSERT INTO CONTACTPHONENR (PHONENR_ID, CONTACT_ID ) VALUES (200064, 200059);
    COMMIT;

    INSERT INTO PHONENR (PHONENR_ID, PHONETYPE_ENUMID, ZONALNR, LOCALNR) VALUES (200061, 3, '061', '3193281');
    INSERT INTO PHONENR (PHONENR_ID, PHONETYPE_ENUMID, ZONALNR, LOCALNR) VALUES (200061, 4, '061', '3193281');
    INSERT INTO PHONENR (PHONENR_ID, PHONETYPE_ENUMID, ZONALNR, LOCALNR) VALUES (200062, 3, '061', '3193281');
    INSERT INTO PHONENR (PHONENR_ID, PHONETYPE_ENUMID, ZONALNR, LOCALNR) VALUES (200063, 4, '061', '3193281');
    COMMIT;


    contact 200057 has two phonenrs, contact 200058 and 200059 each one and contact 200060 has none. More than one phonenr of the same type isn't possible.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "More than one phonenr of the same type isn't possible"

    oh, that's useful to know

    but isn't that rather limiting in the real world?

    and what exactly was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    7

    question

    If it works for more than one type of a phonenr it is ok too.

    My problems:

    -if the contact has a fax and main phonenr, but no mobile phonenr and i select the mobile phonenrs the result is two records without a phoneid in stead of one.
    -if the contact has a fax and main phonenr, but no mobile phonenr and I try to select the fax for this contact, I receive a record with the fax and
    an empty record.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this (again, untested) --
    Code:
    select co.contact_id
         , max(mobilephone.PHONENR_ID) as phone_nr_id
      from contact co 
    inner
      join contactphonenr mobilecoph 
        on co.CONTACT_ID = mobilecoph.contact_id
    left outer
      join phonenr mobilephone 
        on mobilecoph.phonenr_id
         = mobilephone.phonenr_id 
       and mobilephone.phonetype_enumid = 4
     where co.CONTACT_ID = 200057  
    group
        by co.contact_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2005
    Posts
    7

    almost there

    ok, this work fine, but now i want to select the mobilephone.LOCALNR, mobilephone.ZONALNR for this phonenr....

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use MAX() on them too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2005
    Posts
    7

    explanation

    Can someone explain why I receive the wanted record and an unwanted extra empty record?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    impossible, if you are using GROUP BY as i suggested, with nothing else in the SELECT list except the contact id and MAX expressions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2005
    Posts
    7

    part of a bigger query

    This little query is actually part of a bigger query:

    SELECT DISTINCT
    bp.BUSINESSPARTNER_ID, bp.CREDITLIMIT,
    bprole.partnerrole_id,
    cu.tradingname, cu.TAXLIABILITY_ENUMID, cu.REFERENCE, cu.CUSTOMER_ID, 10 as s,
    bu.tradingName, bu.vatNumber,
    co.CONTACT_ID, co.LANGUAGE_ID,
    pe.Title_EnumID, pe.birthday, pe.firstname, pe.lastname,
    cu_gl.ACCOUNT,
    ad.CITY, ad.POSTALCODE, ad.ADDRESSLINE1, ad.ADDRESSLINE2, ad.COUNTRY_ID,
    bankacc.DESCRIPTION, bankacc.ACCOUNTNUMBER,
    bk.TRADINGNAME, bk.BANKIDENTIFIER, bk.SWIFTCODE, adbank.ADDRESSLINE1, adbank.CITY,
    lc.TRANSITIONDATE,

    (select max(mobilephone.LOCALNR)
    from contactphonenr mobilecoph, phonenr mobilephone
    where co.CONTACT_ID = mobilecoph.contact_id(+) and mobilecoph.phonenr_id = mobilephone.phonenr_id
    and mobilephone.phonetype_enumid(+) = 4
    group by mobilecoph.contact_id) as mobileLocal


    FROM
    businesspartner bp, businesspartnerpartnerrole bprole, businessunit bu, contractversion cv,
    person pe, address ad, contactcontactrole ccrole,
    contact co, contactaddress coad, customer cu,glacustomer glac, generalledgeraccount cu_gl,
    bankaccount bankacc, bank bk, businesspartner bpbank, businessunit bubank, contact cobank,
    contactaddress coadbank, address adbank,
    lifecycle lc, relobject relobj
    WHERE
    bp.BUSINESSPARTNER_ID = bprole.BUSINESSPARTNER_ID and bprole.PARTNERROLE_ID = 1
    and bp.BUSINESSUNIT_ID = bu.BUSINESSUNIT_ID and bu.BUSINESSUNIT_ID = co.BUSINESSUNIT_ID
    and co.CONTACT_ID = ccrole.CONTACT_ID and ccrole.CONTACTROLE_ID = 3
    and co.PERSON_ID = pe.PERSON_ID(+) and co.CONTACT_ID = coad.CONTACT_ID(+)
    and coad.ADDRESS_ID = ad.ADDRESS_ID(+) and (coad.ADDRESSLABEL_ENUMID = 160 OR coad.addresslabel_enumid IS NULL)
    and cv.CUSTOMER_ID = bp.BUSINESSPARTNER_ID and cu.BUSINESSPARTNER_ID = bp.BUSINESSPARTNER_ID
    and cu.CUSTOMER_ID = glac.CUSTOMER_ID(+) and glac.GENERALLEDGERACCOUNT_ID = cu_gl.GENERALLEDGERACCOUNT_ID(+)
    and bp.BUSINESSPARTNER_ID = bankacc.BUSINESSPARTNER_ID(+) and bpbank.BUSINESSPARTNER_ID(+) = bankacc.BANK_ID
    and bk.BUSINESSPARTNER_ID(+) = bpbank.BUSINESSPARTNER_ID
    and bpbank.BUSINESSUNIT_ID = bubank.BUSINESSUNIT_ID(+) and bubank.BUSINESSUNIT_ID = cobank.BUSINESSUNIT_ID(+)
    and cobank.CONTACT_ID = coadbank.CONTACT_ID(+) and coadbank.ADDRESS_ID = adbank.ADDRESS_ID(+)
    and (coadbank.ADDRESSLABEL_ENUMID = 160 OR coadbank.addresslabel_enumid IS NULL)
    and (cu.CUSTOMER_ID = relobj.OBJECTID(+) and relobj.LIFECYCLE_ID = lc.LIFECYCLE_ID(+) and
    relobj.SYSREPOBJECT_ID(+) = 331 and lc.TRANSITIONSTATE(+) = 690)

    order by bp.businesspartner_id

    I've tried to integrate the little one in the bigger one, but it doesn't work. What the rest of the query actlly is isn't important. I just have to display some more field of some other tables with the phonenumbers.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks like you forgot a (+)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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