Wow even the 1st query seems to be taking a while...been like 5 minutes.
The DB has "only" around 80,000 records. I do have an index key which is
"email_address_id" so would adding that in the query help speed it up?
Thanks for the quick reply & suggestion...
[EMAIL PROTECTED] wrote:
This query will list all of the email addresses where SOURCE='Source1'
but are not listed within any other source
SELECT et1.email_address
FROM email_table et1
LEFT JOIN email_table et2
on et2.email_address = et1.email_address
and et2.SOURCE <> 'Source1'
WHERE et1.SOURCE = 'Source1'
and et2.email_address is null
There is a more generic version of this but it will respond much
slower as you will create a much larger working table during the
query's JOIN phase:
SELECT et1.email_address
FROM email_table et1
LEFT JOIN email_table et2
on et2.email_address = et1.email_address
and et2.SOURCE <> et1.SOURCE
WHERE et1.SOURCE = 'Source1'
and et2.email_address is null
Either query will respond much faster if there is an index (key) that
has the SOURCE column listed first in its definition.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Rob Kudyba <[EMAIL PROTECTED]> wrote on 11/04/2004 12:47:44 PM:
> The email addresses from source1 that are also not part of all of the
> following sources (source2, source4, and source6).
>
> What I meant was there are addresses that are duplicate and exist in
> source 1, source 2, source 3, et. al. I want to only include the
> distincts from all of them, in other words, ignore the address if it
> also appears in either of the sources other than 1...
> So:
> source 1 has [EMAIL PROTECTED]
> source 2 has [EMAIL PROTECTED]
>
> do no include [EMAIL PROTECTED] in the results of the SELECT...
>
> [EMAIL PROTECTED] wrote:
>
> >
> > So what are you really after:
> >
> > All email addresses that are unique (not multiple-sourced)?
> > The email addresses from source1 that are duplicated from no other
> > sources?
> > The email addresses from source1 that are not from any of the
> > following sources (source2, source4, or source6)?
> > The email addresses from source1 that are also not part of all of the
> > following sources (source2, source4, and source6)?
> >
> > Often questions like this are posted because someone is trying to
> > solve another problem and this is just an intermediate step. If
that's
> > your situation, there may be better ways of achieving your final
> > result than going this route. What is it you eventually want to
> > achieve with this list of email addresses?
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> > Rob Kudyba <[EMAIL PROTECTED]> wrote on 11/04/2004 12:26:42 PM:
> >
> > > I am trying to construct a SELECT statement on distinct email
> > addresses.
> > > Our DB has emails entered by various sources and there are duplicate
> > > addresses based on the source. Using sample table names and sources
> > here
> > > is what I have:
> > >
> > > SELECT DISTINCT email_address
> > > FROM 'email_table'
> > > WHERE SOURCE = 'source 1'
> > >
> > > Simple enough, but I want to exclude email addresses that appear in
> > > several other sources, i.e, something like in this pseudo sample:
> > >
> > > SELECT DISTINCT email_address
> > > FROM [ENTIRE_TABLE_IN_DATABASE]
> > > WHERE SOURCE = 'source 1'
> > > AND EXCLUDE email_address IF SOURCE = [source 2, source 4, source 6]
> > >
> > > Thanks...
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> > >
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
