Hi.
I have four tables: CONTACT, CONT_CORRFAX, CONT_CORREMAIL, and
CONT_CORRLETTER. The tables contain, respectively: contacts, contacts' fax
numbers, contacts' e-mail addresses, and contacts' postal addresses. Each
has a CONTACTID primary key. I'm trying to maximise the data I can pull
together from multiple tables and get one result per CONTACTID.
It is quite possible to have multiple contacts for each "entity" (likely a
company). I'm trying variations on LEFT JOIN. However, this is giving me
four rows per contact. I don't think it's a coincedence that this is the
same as the number of tables I'm joining. Obviously I'm doing something
wrong.
Here's an example of what I get:
CONTACTID FIRSTNAME LASTNAME COMPANY CORRFAX CORREMAIL
ADDRESS
10084 Amos Fishman Amos Fishman Real Estate 1234 5678
[EMAIL PROTECTED] 42 Balmoral Avenue
10084 Amos Fishman Amos Fishman Real Estate 1234 5678
[EMAIL PROTECTED] ?
10084 Amos Fishman Amos Fishman Real Estate 1234 5678
[EMAIL PROTECTED] ?
10084 Amos Fishman Amos Fishman Real Estate 1234 5678
[EMAIL PROTECTED] ?
<other entities/companies...>
and what I'd like to get:
CONTACTID FIRSTNAME LASTNAME COMPANY CORRFAX CORREMAIL
ADDRESS
10084 Amos Fishman Amos Fishman Real Estate 1234 5678
[EMAIL PROTECTED] 42 Balmoral Avenue
<other entities/companies...>
This is the query I am using:
SELECT C.CONTACTID, FIRSTNAME, LASTNAME, COMPANY,
CORRFAX, CORREMAIL,
ADDRESS, STREET, CITY, STATE, POSTCODE, COUNTRY
FROM CONTACT C
LEFT JOIN CONT_CORREMAIL CE
ON C.CONTACTID = CE.CONTACTID
LEFT JOIN CONT_CORRFAX CF
ON CE.CONTACTID = CF.CONTACTID
LEFT JOIN CONT_CORRLETTER CL
ON CF.CONTACTID = CL.CONTACTID
ORDER BY C.CONTACTID
Any clue on how to structure the query? I can always get this working in
code, but I'm fairly certain I should be able to do this in SQL... I'm just
not sure how.
Regards,
David Taylor.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
