Select from multiple tables, remove duplicates
- by staze
I have two tables in a SQLite DB, and both have the following fields:
idnumber, firstname, middlename, lastname, email, login
One table has all of these populated, the other doesn't have the idnumber, or middle name populated.
I'd LIKE to be able to do something like:
select idnumber, firstname, middlename, lastname, email, login
from users1,users2 group by login;
But I get an "ambiguous" error. Doing something like:
select idnumber, firstname, middlename, lastname, email, login from users1
union
select idnumber, firstname, middlename, lastname, email, login from users2;
LOOKS like it works, but I see duplicates. my understanding is that union shouldn't allow duplicates, but maybe they're not real duplicates since the second user table doesn't have all the fields populated (e.g. "20, bob, alan, smith, [email protected], bob" is not the same as "NULL, bob, NULL, smith, [email protected], bob").
Any ideas? What am I missing? All I want to do is dedupe based on "login".
Thanks!
