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

user_10898011 0
Viewed 267

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?

snowflake-cloud-data-platform sql
2 Answers

TABLE1 is not in the main query. It's part of a subquery. If you alias the subquery, you can reference it by the alias.

SELECT "TABLE2".*
FROM (SELECT DISTINCT "Id" FROM DB1."PUBLIC"."TABLE1") T1
INNER JOIN DB1."PUBLIC"."TABLE2" ON T1."Id" = DB1."PUBLIC"."TABLE2"."Id"
WHERE "queryGroupName" not in ('DELETE');
user_127563810

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');
user_50708790
Related