0

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".

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Charles
  • 1
  • 2

2 Answers2

1

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
Siyual
  • 16,415
  • 8
  • 44
  • 58
0

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

Community
  • 1
  • 1
Serg
  • 22,285
  • 5
  • 21
  • 48