join tool - lost data

[vivian123]

Hi all, 

I am using the join tool to combine data however I seem to lose data after joining. 

I would like to join these 2 tables based on PetID. 

Table 1:

OwnerID	Number of pets	PetID	Weight
123	2	111	10
123	2	222	20
234	1	444	40
567	1	555

Table 2: 

PetID	Weight	Length
111	10	50
222	20	60
444	40	70

Ultimately, I want the end product to look like this:

OwnerID	Number of pets	PetID	Weight	Length
123	2	111	10	50
123	2	222	20	60
234	1	444	40	70
567	1	555

After using the join tool, I seem to lose the data for OwnerID 567. What's the correct way to do this? Thanks!

[apathetichell]

Join only combines entries which exist in BOTH data sources (SQL inner join) in the join anchor. If you want to rejoin other entries form one table you'll need to use a union after with say the right (or left) and joined anchors.

[vivian123]

Thanks for the reply. This is still not working though. Could you please show me what the workflow might look like? 

[apathetichell · Accepted solution]

This is a left outer join - so the first thing to do is attach both data sources to a join and match on Pet ID. - taking care to dedupe fields you don't need duplicates of (weight and pet id) - then you take the left anchor and the join anchor and run both into a union. see attached.

[vivian123]

Thanks!!