Hi Holger.
I'm probably not explaining myself very well. Also, I feel it's about time
to make this up-front disclaimer: I did not design this database, I
inherited it. ;-)
Each of CONTACT, CONT_CORREMAIL, CONT_CORRFAX, CONT_CORRLETTER contain
records that contain a CONTACTID. In the CONTACT table, CONTACTID is a
primary key. It is not a primary key in the other tables. There is another
field (CCID) that is the primary key in those other tables.
It seems to me that each contact can have multiple fax numbers, etc. This
makes sense. I discovered a DEFAULT_YN field. It's boolean. There is one
record that has DEFAULT_YN=1 for each group of records per contact in the
CONT_CORR* tables. It seems to me that DEFAULT_YN=1 is used to indicate a
primary fax number, primary e-mail address, etc. It is these "primary"
records that I want to merge with the data I have for each contact in the
CONTACT table. I want to do this, where possible, but I also want to have
results even when a contact has no entries in the CONT_CORRLETTER table, for
example.
Some queries might help clarify things. I have the following for CONTACT:
SELECT COUNT(CONTACTID) FROM CONTACT
1394
SELECT COUNT(DISTINCT(CONTACTID)) FROM CONTACT
1394
I have the following for CONT_CORREMAIL:
SELECT COUNT(CONTACTID) FROM CONT_CORREMAIL
1628
SELECT COUNT(DISTINCT(CONTACTID)) FROM CONT_CORREMAIL
1390
SELECT COUNT(CONTACTID) FROM CONT_CORREMAIL WHERE DEFAULT_YN = 1
1390
I have the following for CONT_CORRFAX:
SELECT COUNT(CONTACTID) FROM CONT_CORRFAX
1508
SELECT COUNT(DISTINCT(CONTACTID)) FROM CONT_CORRFAX
1390
SELECT COUNT(CONTACTID) FROM CONT_CORRFAX WHERE DEFAULT_YN = 1
1390
I have the following for CONT_CORRLETTER:
SELECT COUNT(CONTACTID) FROM CONT_CORRLETTER
5319
SELECT COUNT(DISTINCT(CONTACTID)) FROM CONT_CORRLETTER
1390
SELECT COUNT(CONTACTID) FROM CONT_CORRLETTER WHERE DEFAULT_YN = 1
921
My thanks to everyone for their help so far on this.
Regards,
David Taylor.
----- Original Message -----
From: "Becker, Holger" <[EMAIL PROTECTED]>
To: "'David Taylor'" <[EMAIL PROTECTED]>
Sent: Thursday, July 01, 2004 5:49 PM
Subject: AW: Complex (nested?) left join
Hello David,
seems that I missed something because when CONTACTID is a primary key it's
not possible to get more then 1 result with your join.
Or is CONTACTID only a part of the key ?
Kind regards
Holger
SAP Labs Berlin
> -----Urspr�ngliche Nachricht-----
> Von: David Taylor [mailto:[EMAIL PROTECTED]
> Gesendet: Donnerstag, 1. Juli 2004 07:51
> An: joemoyle; [EMAIL PROTECTED]
> Betreff: Re: Complex (nested?) left join
>
>
> 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]
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
