Trying to find record in one table that is not referenced in another

[SilentCodingOne]

I'm trying to create an exception report for a client that will show them items in one table that do not show up in the other. Basically they have a revenue table and an account table. Data is initially loaded into the revenue table but in order for the data to be loaded correctly certain values in the data need to match data in the Account table. Both tables are large in terms of columns so I will not list them all but give you the important ones. Essentially the SuperEnterpriseId field from revenue should match the SB_Super_Ent_ID__c field in the Account table and the EnterpriseId from the revenue table should also be found as part of the key string in the Account table field SB_Ent_ID_TEST__c, which a combination of a number of fields so it only has to match a portion. If both conditions are not met the record is not loaded properly and would need to appear on the exception report for further review. This is what I most recently have tried but have tried multiple other options as well but nothing is working.

select c.* from "Sound.ads.Revenue" c INNER JOIN "Sound.ads.Account" a ON c.SuperEnterpriseId=a.SB_Super_Ent_ID__c where a.SB_Ent_ID_TEST__c not like '%c.AccountId%';
 

Any ideas?

Thanks