CODEWITHSUNDEEP
mysqlsql
Charles
Charles

Reputation: 1

SQL to join/combine two similiar tables but incomplete into one more complete table

Perhaps it's too complicated to write the problem so I will give you all the picture on how I want the final table looks like:

enter image description here

As you can see there are 3 tables, 1st and 2nd table are similar (have username and email) but unfortunately not all username have email but at least I can fix some username and be able to join all the username without email in one table (like in 3rd table) How can I do this in sql or using query? I tried UNION but it repeat the "a" and "b".

Upvotes: 0

Views: 170

Answers (2)

Serg
Serg

Reputation: 22811

Provided username is unique in both tables, you need FULL JOIN them. And as MySql has no FULL JOIN, you need to mimic it, see https://dba.stackexchange.com/questions/101549/how-to-mimic-a-full-outer-join-using-mysql-views

Upvotes: 0

Siyual
Siyual

Reputation: 16917

This should give you the results you're looking for:

Select      Coalesce(A.UserName, B.UserName) As UserName,
            Coalesce(A.Email, B.Email) As Email
From        TableA  A
Left Join   TableB  B   On  B.UserName = A.UserName
Union
Select      Coalesce(A.UserName, B.UserName) As UserName,
            Coalesce(A.Email, B.Email) As Email
From        TableA  A
Right Join  TableB  B   On  B.UserName = A.UserName

Upvotes: 1

Related Questions