I have a couple of tables where one shows, per record, and ID of a project with the metadata tag of a file. Clients upload multiple files per project ID. When the client uploads a file for a project ID, the file is marked with a tag. Some of those files are mandatory for all project ID’s. There is a second table where it stores all the mandatory tags.

Table 1, tags per ID:

ID  Tag
37616   CORE_CCA
37616   CSHOT_FILE
37616   DRILL_DEV
37616   DRILL_EOWR
37616   DWL_WIRE
37616   GEOL_BIO
37616   GEOL_GEOW
37616   GPHYS_CSHOT
37616   GPHYS_GEN
37616   JWL_AUDIT
37616   JWL_FILE
37616   LOG_COMP
37616   LOG_CORE
37616   LOG_LITH
37616   LOG_MUD
37616   LOG_VEL
37616   LOG_WIRE
37616   VSP_SEGY
37616   WDD_FILE

Table 2, Mandatory tags:

Tag
DRILL_DEV
DRILL_EOWR
GEOL_GEOW
GEOL_MUD
LOG_CASE
LOG_COMP
LOG_MUD
PRE_DPROG
PRE_GPROG
WDD_FILE

I am trying to do a join that shows me the result of all the mandatory tags that I am missing in table1. In this specific case, the result should be:

ID  Tag
31716   GEOL_MUD
31716   LOG_CASE
31716   PRE_DPROG
31716   PRE_GPROG
31716   WDD_FILE

I have tried a left join:

select e.ID, e.tag, m.tag
from table2 m left join table1 e on m.tag = e.tag

But that does not give me what I need. I also added a null value in the where clause for e.tag = null but that returns something else. The last thing I tried was the “exists” clause and that does not return what I need either:

select e.id, e.tag
from table1 e 
where exists (select * from table2 where tag = e.tag)

Any ideas?