DISTINCT from One table and INNER JOIN with another table in snowflake

I would like to:

• Join two tables
• Remove “queryGroupName” = ‘DELETE’ from the first table
• From the first table, get Id by deduping (distinct ID)
• Using this Id, inner join with another table
• For output, select only the second table

SELECT “TABLE2”.*
FROM (SELECT DISTINCT “Id” FROM DB1.”PUBLIC”.”TABLE1″)
INNER JOIN DB1.”PUBLIC”.”TABLE2″ ON DB1.”PUBLIC”.”TABLE1″.”Id” = DB1.”PUBLIC”.”TABLE2″.”Id” WHERE “queryGroupName” not in (‘DELETE’);

I’m getting following error message:

SQL compilation error: error line 4 at position 3 invalid identifier ‘DB1.PUBLIC.TABLE1.”Id”‘

Does anyone know why?

Answer

If the goal is to use JOIN on T1 table only as a filter, IN/EXISTS could be used:

SELECT T2.*
FROM DB1."PUBLIC"."TABLE2" AS T2 
WHERE T2."Id" IN (SELECT T1."Id" FROM DB1."PUBLIC"."TABLE1" AS T1)
  AND T2."queryGroupName" NOT IN ('DELETE');