BigQuery subQueries with join

56 Views Asked by At

I'm using bigQuery i have a table a with column details which is an array of data i want to select this array and every element in this array get an object name from another table: I have use this query

  select a.*,(SELECT a.details,n.name FROM a.details d LEFT JOIN names n ON d.id = n.id ) AS name_details  FROM
                        table1 AS a
                        LEFT JOIN (SELECT * FROM table2) as b
                        ON a.id = b.data_id 

But i got this error :

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

Example


Table1 (
Id  int 
Details Array(id int)
)

Names(
Id int 
Name string
)


table2(
data_id int
)


I want to get 

Table(
Id int (from table1)
Details Array (id ,name (from names table))
)

Any helps please

0

There are 0 best solutions below