In most cases that would give me two results per contact. I want to merge the data. I want a SELECT that gives me one result for each contact in the CONTACT table, with additional data, where possible, obtained from CONT_CORRFAX, CONT_CORREMAIL, and CONT_CORRLETTER. While I'm not sure how to do this, I have tried variations of my queries with DISTINCT and I know they do not give me what I want.
Thanks anyway. Regards, David Taylor. ----- Original Message ----- From: "joemoyle" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, June 30, 2004 11:55 PM Subject: Re: Complex (nested?) left join > 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] > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
