CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
knozawa
knozawa

Reputation: 283

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

I would like to:

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?

Upvotes: 2

Views: 1502

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

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

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11046

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

Upvotes: 1

Related Questions