CODEWITHSUNDEEP
joinsnowflake-cloud-data-platform
Jonito
Jonito

Reputation: 459

Inner join return duplicated record

My table:

Report_Period Entity Tag Users Count Report_Period_M-1 Report_Period_Q-1 ...
2017-06-30 entity 1 X 471 2017-05-31 2017-03-31 ...
2020-12-31 entity 2 A 135 2020-11-30 2020-09-30 ...
2020-11-30 entity 3 X 402 2020-10-31 2020-08-31 ...

What I want:

Report_Period Entity Tag Users Count Users_Count_M-1 Users_Count_Q-1 ...
2017-06-30 entity 1 X 471 450 438 ...
2020-12-31 entity 2 A 135 122 118 ...
2020-11-30 entity 3 X 402 380 380 ...

I have have tried this code but it duplicate records! How can I avoid it?

SELECT M."Entity",M."Tag",M."Report_Period",M."Users Count",
M."Report_Period_M-1",M1."Users Count" AS "Users Count M1",
FROM "DB"."SCHEMA"."PERIOD" M, "DB"."SCHEMA"."PERIOD" M1
WHERE M."Report_Period_M-1"= M1."Report_Period"

Upvotes: 0

Views: 194

Answers (1)

Rob Silva
Rob Silva

Reputation: 76

Your join clause should include the entity column and tag (I suspect)

SELECT    M."Entity",
          M."Tag",
          M."Report_Period",
          M."Users Count",
          M."Report_Period_M-1",
          M1."Users Count" AS "Users Count M1",  
FROM      "DB"."SCHEMA"."PERIOD" M, 
         "DB"."SCHEMA"."PERIOD" M1 
WHERE    M."Report_Period_M-1"= M1."Report_Period"
AND      M."Entity" = M1."Entity"
AND      M."Tag" = M1."Tag"

Upvotes: 1

Related Questions