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?

try this (untested) --

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

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?

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.

"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?

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.

try this (again, untested) --

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

use MAX() on them too

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

impossible, if you are using GROUP BY as i suggested, with nothing else in the SELECT list except the contact id and MAX expressions

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.

looks like you forgot a (+)