Combine 2 sets of data

[traceystone]

I have 2 spreadsheets that I would like to append the columns and align by a unique identifier. Here is an example of the 2 files and the desired output. I tried to us the join but it's not working for me. The join is only returning 2 records that match instead of 5 records with the added columns and information for those names.

File 1
SAMACCOUT	Name
u12342	Bob Jones
u12343	Jane Doe
u12344	John Smith
u12345	Jason Dill
u12346	Jill Davis
File 2
Domain ID	Name	Status	Date
u12343	Jane Doe	Hire	2/3/2022
u12344	John Smith	Terminated	2/22/2022
Output File
Samaccout	Name	Status	Date
u12342	Bob Jones
u12343	Jane Doe	Hire	2/3/2022
u12344	John Smith	Terminated	2/22/2022
u12345	Jason Dill
u12346	Jill Davis

[Qiu]

@traceystone 
We can use union the left anchor of the Join tool.

[amruthas2]

Hi @traceystone 

Use join and then union it with the L - 'left unjoined' and J - 'inner join', if you see anything missing from R- 'right joined' you can connect that table aswell with union.