Select from multiple tables, remove duplicates
I have two tables in SQLite DB and both have the following fields:
idnumber, firstname, middlename, lastname, email, login
One table has all these settlements, the other does not have an ID or middle name.
I would like to do something like:
select idnumber, firstname, middlename, lastname, email, login
from users1,users2 group by login;
But I am getting 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;
LOOK how it works, but I see duplicates. I understand that concatenation should not allow duplicates, but maybe they are not real duplicates since the second users table does not have all the fields filled in (eg "20, bob, alan, smith, bob@bob.com , bob" is not the same as "NULL, bob, NULL, smith, bob@bob.com , bob").
Any ideas? What am I missing? All I want to do is dedupe based on "input".
Thank!
source to share
As you say, union
will remove duplicate entries (note that union all
it won't!). Two records are considered duplicate when all of their column values match. In the example you looked at in your question, it is clear what is NULL
not equal to 20
or 'alan'
, so these entries will not be treated as duplicates.
Edit:
[...] the only way I can think of would be to create a new table [...]
It's not obligatory. I think you can do the following:
select login, max(idnumber), max(firstname), max(middlename), max(lastname),
max(email) from (
select idnumber, firstname, middlename, lastname, email, login from users1
union
select idnumber, firstname, middlename, lastname, email, login from users2
) final
group by login
However, if you are sure that you only have different values on idnumber
and middlename
, you can maximize only those fields and group by everyone else.
source to share