Left outer join problem

[snauw]

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? And tell me why I receive two records in stead of one.

[sde]

should there be an 'and' in the 'on' clause? without your data schema it's hard to figure out what you're doing. .. i just tried to have a wack at it and i am totally lost with your table names and that join syntax.

what db are you using? and maybe tell a little bit more about the tables you are querying.

[snauw]

Like this you can 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;

I'm using Oracle.

[sde]

SELECT C.CONTACT_ID, P.PHONENR_ID, P.PHONETYPE_ENUMID
  FROM CONTACT AS C
  LEFT JOIN CONTACTPHONENR AS CP ON C.CONTACT_ID=CP.CONTACT_ID
  LEFT JOIN PHONENR AS PN ON CP.PHONENR_ID=PN.PHONENR_ID
  WHERE PN.PHONETYPE_ENUMID=4
    AND C.CONTACT_ID=20057

i'm just guessing here, but if that doesn't work, maybe add a 'GROUP BY C.CONTACT_ID'

i'm not sure. if you find the answer, i'd be interested to know what works.


from contact co ,
contactphonenr mobilecoph left join phonenr mobilephone on
mobilephone.phonenr_id = mobilecoph.phonenr_id and mobilephone. and co.CONTACT_ID = mobilecoph.contact_id

[snauw]

SELECT mobile.local, mobile.zonal
FROM
contact,
(select mobilecoph.contact_id as contact_id, max(mobilephone.PHONENR_ID), max(mobilephone.LOCALNR) as local, max(mobilephone.ZONALNR) as zonal
from contactphonenr mobilecoph, phonenr mobilephone
where mobilecoph.phonenr_id = mobilephone.phonenr_id(+)
and mobilephone.phonetype_enumid(+) = 4
group by mobilecoph.contact_id) mobile
WHERE mobile.contact_id(+) = co.CONTACT_ID
and co.CONTACT_ID = 200057