I'm brand new to MaxDB but I immediately wonder if there is a DISTINCT clause that can be used. Something like select distinct <column list> from <table list> where <clause> -----Original Message----- >From: David Taylor<[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >CC: >Sent: Tuesday, June 29, 2004 10:43:00 PM >Subject: Complex (nested?) left join 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]
