CODEWITHSUNDEEP
sqlgoogle-bigquery
ORCos
ORCos

Reputation: 99

How can I delete duplicate rows when joining two tables and condition is met

I have two tables in BigQuery with the following columns

t1

timestamp |customer_id |office |client_name
01.01.2020|1           |USA    |Mike
01.01.2020|2           |USA    |John
01.01.2020|3           |GER    |Jenny`

t2

timestamp |customer_id |agent_id |agent_role
01.01.2020|1           |10       |Level1
01.01.2020|1           |20       |Level2
01.01.2020|2           |30       |Level1`

Now when I join this two tables 

SELECT DISTINCT t1.timestamp, t2.customer_id, office, client_name,
        CASE WHEN role = "Leve1" THEN agent_id ELSE NULL END
        AS responsible_agent, 
FROM t1 
   LEFT JOIN t2 ON t1.customer_id = t2.customer_id

I get the following table

t3

timestamp |customer_id |office |client_name|agent_id
01.01.2020|1           |USA    |Mike       | 10
01.01.2020|1           |USA    |Mike       |null
01.01.2020|2           |USA    |Jenny      | 30`

My new table (t3) should display the agent_id if there is a Level 1 assigned, null if there is no Level 1 assigned and nothing more. As you can see in t3 i get two records for customer_id = 1 because customer_id = 1 has an agent_role = Level 1 but also a agent_role = Level 2 assigned.

t3

timestamp |customer_id |office |client_name|agent_id
01.01.2020|1           |USA    |Mike       | 10
01.01.2020|1           |USA    |Mike       |null

My quest is to join t1 and t2 .. display the agent_id if there is a Level 1 , display null if there is no Level 1 assigned and remove the ones that are display null because there is a Level 2 assigned.

Upvotes: 0

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

If I understand correctly:

SELECT t1.timestamp, t1.customer_id, t1.office, t1.client_name,
       COALESCE(MAX(CASE WHEN t2.role = 'Leve1' THEN t2.agent_id END) , 
                MAX(t2.agent_id)
               ) AS responsible_agent,
FROM t1 LEFT JOIN
     t2
     ON t1.customer_id = t2.customer_id
GROUP BY t1.timestamp, t1.customer_id, t1.office, t1.client_name;

Upvotes: 1

Mansi
Mansi

Reputation: 153

SELECT DISTINCT t1.timestamp, t1.customer_id, office, client_name,t2.agent_id 
FROM t1 
LEFT JOIN t2 ON t1.customer_id = t2.customer_id AND t2.agent_role = 'Level1'

Upvotes: 0

Related Questions