I need help with selecting from 2 identical tables in 2 separate databases

[phillip.s.powell]

MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't
ask)

I have database Spring with table Students

I have database Summer with table Students

I am tasked to produce a query of all students in both tables with no
duplicates. No clue whatsoever.

I'm thinking an INTERSECT followed by 2 UNION SELECT statements, but
how far off am I? The tables are absolutely identical in every way
including indexes; the data MIGHT be identical (same data might be in
Spring.Students and Summer.Students ), however, that won't always be the
case. The column Spring.Students .id will not share the same value as
Summer.Students .id as data can be entered within separate sequences
even if the data in both tables is absolutely identical.

Am I on the right track with INTERSECT/UNION or what do you recommend?

Thanx
Phil

[Markus Popp]

> It turns out that trying to add a table comment to MyISAM tables

version 7 with 5.0.15 destroys them. dumping/restoring the tables
produce a mixture of version 9 or 10. (You can see the version
number of a table with SHOW TABLE STATUS.)

This is interesting, because new to me. Do you know if that problem still
exists in the new table versions? I also seem to have a mixture of version 9
and 10. Is that a problem?

Markus

[phillip.s.powell]

Sorry, the "distinct" keyword failed to produce truly unique records.
The Spring.Students table (and the Summer.Students table) has about 75
columns, 74 of them are identical, the "id" column is unique.

I did find a rather novel approach to this, however:

[SQL]

-- field `unique_key` will contain a 16-character random alphanumeric
string for repost protection
ALTER TABLE Spring.Students ADD UNIQUE (`unique_key`);

ALTER TABLE Summer.Students ADD UNIQUE (`unique_key`);

DROP TABLE /*! IF EXISTS */ AllStudents.spr ing_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spr ing_summer SELECT *
FROM Spring.Students LIMIT 1;

DELETE FROM AllStudents.spr ing_summer;

INSERT INTO AllStudents.spr ing_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students ;

[/SQL]

This produces the following syntax error:

Syntax error near 'UNION SELECT * FROM Summer.Students '

What gives? It was working over the weekend and now fails? WHY?

Phil
Markus Popp wrote:

Use the DISTINCT keyword:

select distinct col1, col2 from spring_summer; (list all the columns that
are non-unique, so leave out the ID column)

There are also ways to eliminate the duplicate records, but (with versions
before 4.1) that would require that you create another (temporary) table.
This looks something like this:

create table temp_table select min(id) as id, col1, col2 from spring_summer
group by col1, col2;

This produces a temporary table with all unique records with their lowest ID
(you could also use max(id) to get the highest ID).

Then you can join the temp_table with the spring_summer to find all records
that have another ID than those saved in the temp_table. Look at this:

mysql> select * from tbl;
+----+---+
| id | d |
+----+---+
| 1 | a |
| 2 | a |
| 3 | b |
| 4 | b |
| 5 | b |
| 6 | c |
| 7 | c |
+----+---+
7 rows in set (0.01 sec)

mysql> create table temp_table select min(id) as id, d from tbl group by d;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from temp_table;
+------+---+
| id | d |
+------+---+
| 1 | a |
| 3 | b |
| 6 | c |
+------+---+
3 rows in set (0.00 sec)

mysql> delete a from tbl a left join temp_table b on a.id = b.id where b.id
is null;
Query OK, 4 rows affected (0.00 sec)

I'm not sure if the last query works with MySQL 3.23. If it doesn't, it
would take some other extra steps (that's were we see the benefits of MySQL
4.1 and 5.0, where this all could be done in a single statement).

Markus

[phillip.s.powell]

Ok UPDATE:

We upgraded to MySQL 5.0 and I tried this, and this also failed to
function:

[SQL]
DROP TABLE /*! IF EXISTS */ AllStudents.spr ing_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spr ing_summer SELECT *
FROM OrigStudents.st udents LIMIT 1;

DELETE FROM AllStudents.spr ing_summer;

ALTER TABLE AllStudents.spr ing_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spr ing_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students ;
[/SQL]

This in MySQL 5.0 throws an error after only inserting one record:

Duplicate entry 'asdfasdf' for key 1

I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!

Phil

[Gordon Burditt]

>Ok UPDATE:

We upgraded to MySQL 5.0 and I tried this, and this also failed to
function:

[SQL]
DROP TABLE /*! IF EXISTS */ AllStudents.spr ing_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spr ing_summer SELECT *
FROM OrigStudents.st udents LIMIT 1;

DELETE FROM AllStudents.spr ing_summer;

ALTER TABLE AllStudents.spr ing_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spr ing_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students ;

Use INSERT IGNORE here and let the errors happen, but it should
keep going..

Gordon L. Burditt

[Bill Karwin]

ph************* *@gmail.com wrote:

I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!

You need to get help from the teacher. By the way, internet etiquette
generally frowns upon students trying to get help with their homework on
newsgroups.

Bill

[phillip.s.powell]

Ok UPDATE:

We upgraded to MySQL 5.0 and I tried this, and this also failed to
function:

[SQL]
DROP TABLE /*! IF EXISTS */ AllStudents.spr ing_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spr ing_summer SELECT *
FROM OrigStudents.st udents LIMIT 1;

DELETE FROM AllStudents.spr ing_summer;

ALTER TABLE AllStudents.spr ing_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spr ing_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students ;
[/SQL]

This in MySQL 5.0 throws an error after only inserting one record:

Duplicate entry 'asdfasdf' for key 1

I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!

Phil

[phillip.s.powell]

There is no teacher, class, whatever. This is work and it's due Monday
AM!

Phil
Bill Karwin wrote:

ph************* *@gmail.com wrote:

I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!

You need to get help from the teacher. By the way, internet etiquette
generally frowns upon students trying to get help with their homework on
newsgroups.

Bill

[Bill Karwin]

ph************* *@gmail.com wrote:

INSERT INTO AllStudents.spr ing_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students ;

This in MySQL 5.0 throws an error after only inserting one record:

Duplicate entry 'asdfasdf' for key 1

Well, this means that there are some entries in Spring.Students that
also exist in Summer.Students . Therefore you get a conflict as soon as
the insert reaches the first duplicate (maybe the first record returned
by the second half of the UNION).

At the beginning of this thread I suggested a query construction that
should work. Here it is again, with the table names matching your query
above.

INSERT INTO AllStudents.spr ing_summer
(SELECT S1.*
FROM Spring.Students AS S1 INNER JOIN Summer.Students AS U1 ON
S1.unique_key = U1.unique_key)
UNION
(SELECT S2.*
FROM Spring.Students AS S2 LEFT OUTER JOIN Summer.Students AS U2 ON
S2.unique_key = U2.unique_key
WHERE U2.unique_key IS NULL)
UNION
(SELECT U3.*
FROM Spring.Students AS S3 RIGHT OUTER JOIN Summer.Students AS U3 ON
S3.unique_key = U3.unique_key
WHERE S3.unique_key IS NULL);

Regards,
Bill K.

[phillip.s.powell]

You know the old adage, "Ignore the problem and it'll go away"? That's
exactly what I did, and everything worked:

DROP TABLE AllStudents.stu dents;
CREATE TABLE AllStudents.stu dents SELECT * FROM Spring.Students LIMIT
1;
ALTER TABLE AllStudents.stu dents ADD UNIQUE (email);
ALTER TABLE AllStudents.stu dents ADD UNIQUE (unique_key);
INSERT IGNORE INTO AllStudents.stu dents SELECT * FROM Spring.Students
UNION DISTINCT SELECT * FROM Summer.Students ;

That did the trick! In short, enforce uniqueness on 2 of the columns
that should be unique, which ensures "duplicates " are never added while
warnings are thrown out, so that all unique records are combined into
one table!

The only stipulation was that we had to do an enforced upgrade to MySQL
5.0 to ensure this would work.

Phil

Bill Karwin wrote:

ph************* *@gmail.com wrote:

INSERT INTO AllStudents.spr ing_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students ;

This in MySQL 5.0 throws an error after only inserting one record:

Duplicate entry 'asdfasdf' for key 1

Well, this means that there are some entries in Spring.Students that
also exist in Summer.Students . Therefore you get a conflict as soon as
the insert reaches the first duplicate (maybe the first record returned
by the second half of the UNION).

At the beginning of this thread I suggested a query construction that
should work. Here it is again, with the table names matching your query
above.

INSERT INTO AllStudents.spr ing_summer
(SELECT S1.*
FROM Spring.Students AS S1 INNER JOIN Summer.Students AS U1 ON
S1.unique_key = U1.unique_key)
UNION
(SELECT S2.*
FROM Spring.Students AS S2 LEFT OUTER JOIN Summer.Students AS U2 ON
S2.unique_key = U2.unique_key
WHERE U2.unique_key IS NULL)
UNION
(SELECT U3.*
FROM Spring.Students AS S3 RIGHT OUTER JOIN Summer.Students AS U3 ON
S3.unique_key = U3.unique_key
WHERE S3.unique_key IS NULL);

Regards,
Bill K.