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!
Solved! Go to Solution.
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.
Thanks for the reply. This is still not working though. Could you please show me what the workflow might look like?
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.
Thanks!!