Merge Two Tables with Left Outer Join While Keeping All Rows From First Table (Matched or Unmatched)

[RokuCap]

I have two tables. Table A contains a repeating group rank 1-4 (secondary key) based on the value in another column (primary key). I want to use the Left Outer Join Merge function to add/return the matching records from Table B into Table A (similar to a lookup function in Excel). The reason for left outer join is it is supposed to 'keep all from the first, matching from second'.

The issue is not all rows in Table A match a row from Table B (e.g. "the selection matches 285 of 380 rows from the first table"). So when I try to expand the merged column, the unmatched rows from Table A disappear from the final result. I've unsuccessfully tried the other merge join kinds such as 'Right Outer' and 'Full Outer'. Is there a way to merge matching rows from Table B while keeping the unmatched rows from Table A?

As an example:

Thanks in advance!

[HotChilli · Accepted solution]

We'll need some more detail please to find out what's going on.  Experience tells us that a left-outer join will correctly retain unmatched rows from the 'left' table.

If you want more help, please provide a small sample that illustrates the problem or provide some pictures of the before and after effects of the join (with a picture of the Merge join screen)

[RokuCap]

Thanks for the reply. You were right! The left outer join should retain the unmatched rows.

I remembered that merging tables will sometimes jumble the order of the rows so I sorted the primary key column and all the unmatched rows were there.