| Bill Karwin 2005-11-17, 8:23 pm |
| phillip.s.powell@gmail.com wrote:
> Bad news.. the query doesn't work after all.. turns out that the query
> only produces data from Spring.Students and from data found in both
> Spring.Students and Summer.Students; all data unique to Summer.Students
> is not copied over. :(
It also treats John Smith and James Smith as the same person.
You're almost there.
First, get the folks who are in both spring and summer.
CREATE TABLE blah
SELECT DISTINCT s.*
FROM db1.table1 s INNER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
Next, get the folks who are only in spring.
INSERT INTO blah
SELECT DISTINCT s.*
FROM db1.table1 s LEFT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE u.first_name IS NULL;
Next, get the folks who are only in summer.
INSERT INTO blah
SELECT DISTINCT u.*
FROM db1.table1 s RIGHT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE s.first_name IS NULL;
Regards,
Bill K.
|