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!