Using mysql 5.02/nt

I have a project that requires me to combine 22 subscriber lists
(tables) into one master table, so we can export our lists to a new
management system.

The tables are mostly similar, with about 40 fields each.
One field contains the email address, which is unique in each table.

The final table should contain a unique list of all email addresses,
retaining as much detail from the original tables as possible.

The list of tables was given to me in priority order, with the desire,
that records in subsequent tables would be included in the destination
table if they did not already exist.

I have limited experience in MySQL, combining two tables to 1 report,
and then loading into a new table. I could do this 22 times, but
there has to be an easier way.

pseudo code:

foreach table a,b,c,d,e,f ....
insert (list of fields) into destination table
where source.email notin destination.email

Previously I have used:

SELECT
gt.email // plus the list of fields
FROM
gtweekly AS gt
Left Join master ON gt.email = master.email
WHERE
master.email IS NULL

How do I insert these "found" records back into the master list, and
iterate through the list of tables..

On 10 Apr, 14:36, Jim Britain <nomail...@suddenlink.net> wrote:
> Using mysql 5.02/nt
>
> I have a project that requires me to combine 22 subscriber lists
> (tables) into one master table, so we can export our lists to a new
> management system.
>
> The tables are mostly similar, with about 40 fields each.
> One field contains the email address, which is unique in each table.
>
> The final table should contain a unique list of all email addresses,
> retaining as much detail from the original tables as possible.
>
> The list of tables was given to me in priority order, with the desire,
> that records in subsequent tables would be included in the destination
> table if they did not already exist.
>
> I have limited experience in MySQL, combining two tables to 1 report,
> and then loading into a new table. I could do this 22 times, but
> there has to be an easier way.
>
> pseudo code:
>
> foreach table a,b,c,d,e,f ....
> insert (list of fields) into destination table
> where source.email notin destination.email
>
> Previously I have used:
>
> SELECT
> gt.email // plus the list of fields
> FROM
> gtweekly AS gt
> Left Join master ON gt.email = master.email
> WHERE
> master.email IS NULL
>
> How do I insert these "found" records back into the master list, and
> iterate through the list of tables..

How "mostly similar" are these tables?

You could use INSERT IGNORE ... SELECT
or INSERT .. SELECT ... ON DUPLICATE KEY UPDATE ...

On Thu, 10 Apr 2008 06:53:33 -0700 (PDT), Captain Paralytic
<paul_lautman@yahoo.com> wrote:

>On 10 Apr, 14:36, Jim Britain <nomail...@suddenlink.net> wrote:
>> Using mysql 5.02/nt
>>
>> I have a project that requires me to combine 22 subscriber lists
>> (tables) into one master table, so we can export our lists to a new
>> management system.
>>
>> The tables are mostly similar, with about 40 fields each.
>> One field contains the email address, which is unique in each table.
>>
>> The final table should contain a unique list of all email addresses,
>> retaining as much detail from the original tables as possible.
>>
>> The list of tables was given to me in priority order, with the desire,
>> that records in subsequent tables would be included in the destination
>> table if they did not already exist.
>>
>> I have limited experience in MySQL, combining two tables to 1 report,
>> and then loading into a new table. I could do this 22 times, but
>> there has to be an easier way.
>>
>> pseudo code:
>>
>> foreach table a,b,c,d,e,f ....
>> insert (list of fields) into destination table
>> where source.email notin destination.email
>>
>> Previously I have used:
>>
>> SELECT
>> gt.email // plus the list of fields
>> FROM
>> gtweekly AS gt
>> Left Join master ON gt.email = master.email
>> WHERE
>> master.email IS NULL
>>
>> How do I insert these "found" records back into the master list, and
>> iterate through the list of tables..
>
>How "mostly similar" are these tables?
>
>You could use INSERT IGNORE ... SELECT
>or INSERT .. SELECT ... ON DUPLICATE KEY UPDATE ...

I can drop the right most columns from the selection, where they are
different. on 18 of the 22 tables, they only differ in the last 3 or 4
fields, and it is not important to carry them over to the new
subscription management system. On the last 4 tables in the set, I'll
have to do them individaully, as there are substantial differences.
But they also are the shortest lists.. and likely will only be a
couple records each, after eliminating the duplicates.

There are significant overlaps in duplicate email addresses, which are
also the primary key for the subscription lists. With a total number
of about 100,000 records, a unique sort on email addresses results in
27,000 unique records.

On 10 Apr, 15:08, Jim Britain <nomail...@suddenlink.net> wrote:
> On Thu, 10 Apr 2008 06:53:33 -0700 (PDT), Captain Paralytic
>
>
>
> <paul_laut...@yahoo.com> wrote:
> >On 10 Apr, 14:36, Jim Britain <nomail...@suddenlink.net> wrote:
> >> Using mysql 5.02/nt
>
> >> I have a project that requires me to combine 22 subscriber lists
> >> (tables) into one master table, so we can export our lists to a new
> >> management system.
>
> >> The tables are mostly similar, with about 40 fields each.
> >> One field contains the email address, which is unique in each table.
>
> >> The final table should contain a unique list of all email addresses,
> >> retaining as much detail from the original tables as possible.
>
> >> The list of tables was given to me in priority order, with the desire,
> >> that records in subsequent tables would be included in the destination
> >> table if they did not already exist.
>
> >> I have limited experience in MySQL, combining two tables to 1 report,
> >> and then loading into a new table. I could do this 22 times, but
> >> there has to be an easier way.
>
> >> pseudo code:
>
> >> foreach table a,b,c,d,e,f ....
> >> insert (list of fields) into destination table
> >> where source.email notin destination.email
>
> >> Previously I have used:
>
> >> SELECT
> >> gt.email // plus the list of fields
> >> FROM
> >> gtweekly AS gt
> >> Left Join master ON gt.email = master.email
> >> WHERE
> >> master.email IS NULL
>
> >> How do I insert these "found" records back into the master list, and
> >> iterate through the list of tables..
>
> >How "mostly similar" are these tables?
>
> >You could use INSERT IGNORE ... SELECT
> >or INSERT .. SELECT ... ON DUPLICATE KEY UPDATE ...
>
> I can drop the right most columns from the selection, where they are
> different. on 18 of the 22 tables, they only differ in the last 3 or 4
> fields, and it is not important to carry them over to the new
> subscription management system. On the last 4 tables in the set, I'll
> have to do them individaully, as there are substantial differences.
> But they also are the shortest lists.. and likely will only be a
> couple records each, after eliminating the duplicates.
>
> There are significant overlaps in duplicate email addresses, which are
> also the primary key for the subscription lists. With a total number
> of about 100,000 records, a unique sort on email addresses results in
> 27,000 unique records.

The answers remain the same. I would use the former in priority order
high->low for the bulk and the latter in the revers order for the
substantial differences

On Thu, 10 Apr 2008 07:39:15 -0700 (PDT), Captain Paralytic

>The answers remain the same. I would use the former in priority order
>high->low for the bulk and the latter in the revers order for the
>substantial differences

Thanks, I guess I can go over the tables 1 at a time rather than
trying to build come kind of loop. -- it's about data, not procedure
-- which is a real mindshift for me.

I guess I can stack select statements one after the other? For each
table?

Jim Britain wrote:
> On Thu, 10 Apr 2008 07:39:15 -0700 (PDT), Captain Paralytic
>
>>The answers remain the same. I would use the former in priority order
>>high->low for the bulk and the latter in the revers order for the
>>substantial differences
>
> Thanks, I guess I can go over the tables 1 at a time rather than
> trying to build come kind of loop. -- it's about data, not procedure
> -- which is a real mindshift for me.
>
> I guess I can stack select statements one after the other? For each
> table?

Yep 22 tables = 22 queries