Finding entries in one table but not another: emulate an outer join?

[Noah_Glaser]

I have two tables, one that is built in-house (say, INTERNAL) and one that is supplied externally (say, EXTERNAL).

I don’t trust my external data source, so what I want to do is find all records present in INTERNAL and missing from EXTERNAL.

If I were working in SQL, the query I would write would be something along the lines of:

SELECT a.*, b.* from INTERNAL AS a
LEFT JOIN EXTERNAL as b
ON a.primary_key=b.primary_key
where b.primary_key IS NULL

I want to avoid using record linking; there are over 100 records to do this for, and my understanding of linked records is that they have to be done manually. Having to do this manually would defeat the entire purpose of using a database tool to speed up my workflow.

Can the above analysis be done strictly within Airtable, or will I need to write an API script to emulate a JOIN?

[Mohamed_Swella1]

Hi @Noah_Glaser,

Welcome to Airtable!

First thing, the linked field does not have to be done manually. However, the restriction would be that you cannot link the first field (Primary field) in the table. And the field that is linked in the first table is always the Primary field.

So, what I would do if I want to check if the items are the same, I would do the following:

1. If the items in table 2 that needs to be linked are in the Primary Field, copy them to the 2nd field. Of they are not in the first field, skip this step.

2. If the items in table 1 that are similar to table 2 are in any other field except the Primary field, copy them to the first field.

3. Link the field in table 2 to table 1 and it will link automatically. If the item is not present in table 1, this will create a mew record, so take care.

Would that help?

BR,
Mo

[Noah_Glaser]

@Mohamed_Swellam I tried what you suggested and it’s not telling me what I want to know.

What I want to know is: which records (by primary key) are present in Table 1, but not Table 2.

I created the linked field as suggested, but the linked field didn’t automatically populate with any information, nor did any new records appear as you indicated they would.

I still have no idea how to figure out which records are present in one table, but absent in another.

[Mohamed_Swella1]

Hi @Noah_Glaser,

Please attach a screenshot of both tables and tell me which fields you want to get the data from exactly so I can help you better.

BR,
Mo

[Jeremy_Oglesby]

@Noah_Glaser

I’m not sure I really understand the why of your use case, but I believe the Scripting Block could provide the output you are wanting (provided both tables you are talking about are within the same base).

Do you have access to blocks in your workspace?

[Noah_Glaser]

@Jeremy_Oglesby

The why of our use case is essentially that we are a labor union. So we have two different rosters of members, one that we maintain in-house and one we get from the employer. We don’t trust the employer’s list, so we need to find membership discrepancies between the two lists.

I do have access to blocks, and will look into the scripting block’s capabilities.

[kuovonne · Accepted solution]

If you don’t want to write a script, here is one method:

Let’s say you have two tables [Internal] and [External] in the same base. You have the field {InternalNames} as the primary field in the [Internal] table. You have the filed {ExternalNames} as the primary field in the [External] table. All of the {ExternalNames} are also {InternalNames}, but the records are not yet linked. There are also some {InternalNames} that are not {ExternalNames}

1. In the [External] table, create a new field named {Link} that is a link to a record in the [Internal] table. Copy and paste all the values from {ExternalNames} to the newly created {Link}. You can do this in one copy/paste. The records are now linked.

2. Switch to the [Internal] table. There will now be a new field showing the link to the [External] table. If there was a matching record, there will be a value in the link. If there was no matching record, the new field will be empty. Filter the [Internal] table to show records where the new field is empty.