CODEWITHSUNDEEP
sqljoingoogle-bigquerystring-aggregation
Nick G
Nick G

Reputation: 23

BigQuery SQL - Combine multiple rows from 1 column that match rows from another table?

I'm new to Big Query and SQL in general, having really on spent a few days with it so far. I've spent quite a few hours on my current goal and can't seem to find a way to achieve my objective...
I think I've got the Concat right, but am getting duplicated that I can't get rid off as there's one row for each SPECID.
Also not sure once I get rid of them, how I join in the third table without more duplicates?

DATA:

t1  
SUMID   Reference   Type    Size
1001    234         Round   120
1002    234         Square  60

t2                      
DETID   SUMID   SPECID  Value
2001    1001    1       TRUE
2002    1001    2       TRUE
2003    1001    3       FALSE
2004    1001    4       TRUE
2005    1001    5       FALSE
2006    1001    6       TRUE
2007    1002    1       FALSE
2008    1002    2       TRUE
2009    1002    3       FALSE
2010    1002    4       TRUE
2011    1002    5       TRUE
2012    1002    6       TRUE

t3          
SPECID  Description     
1       Blue        
2       Red     
3       Green       
4       Orange      
5       Yellow      
6       Purple

OUTPUT 

t1.Reference    t1.Type  t1.Size  t3.Description
234             Round     120     Blue, Red, Orange, Purple
234             Square     60     Red, Orange, Yellow, Purple

I've made it as far as the following, but for the life of me, I can't get my head around it.

#standardSQL
SELECT 
t1.SUMID, t1.Reference, t1.Type, t1.Size, t2.Description

FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.T2_ID = t2.T2_ID

LEFT JOIN (
SELECT *
FROM ( 
select SUMID, Value,
  CONCAT(
    STRING_AGG(CAST(SPECID AS STRING), ', ') OVER(PARTITION BY SUMID, Value) 
        ) SPEC_IDs

from Table2
)) t2 ON t1.SUMID = t2.SUMID

WHERE t2.Value = TRUE

If anyone could break this down for me I'd be really appreciative? I'm hoping once I see how it works it'll just click.

Upvotes: 0

Views: 488

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

If I understand correctly, this is some JOINs with aggregation:

SELECT t1.Reference, t1.Type, t1.Size,
       STRING_AGG(t3.Description, ', ')
FROM Table1 t1 LEFT JOIN
     Table2 t2
     ON t2.T2_ID = t1.T2_ID AND 
        t2.Value LEFT JOIN
     Table3 t3
     ON t3.SPECID = t2.SPECID
GROUP BY t1.Reference, t1.Type, t1.Size;

Upvotes: 1

Related Questions