SQL Bigquery - Create a data set from 3 tables with master records and no duplicates with ARRAY_AGG(Struct)

Question

I am trying to query 3 tables to create one data set with master item data, transaction data and then serial numbers/locations. My left table is a unique key for each item mentioned once, then the transaction data and serial data has the key repeating many times and I would like them to be a collection under the item for use in Looker Studio.

I have this working for Item to Transaction and Item to Serial but when I put all together I get loops and repeats of Data.

Here is an example of the tables enter image description here

Here is the desired output enter image description here

ItemCode
Manufacturer
Model
Qty on Order
Transaction
    Date
    Qty
Location
    WH-Bin
    Serial
    Qty

Here is the code I have working to join two of the tables...

with nested as (

  SELECT
    i.ItemCode,i.Manuf,i.ItemName,i.QtyonOrder,i.AvgPrice,
    array_agg(struct (t.Date, t.Quanitty)) as Transactions, 

  FROM
    ItemMasterData i

  Left join
    Transactions t on i.ItemCode = t.ItemCode

  group by i.ItemCode,i.Manuf,i.ItemName,i.QtyonOrder,i.AvgPrice,
)

 
Select * from nested,

When I tied adding another

array_agg(struct (l.WHBin, l.Serial, l.Qty)) as Transactions,

Left Join Transactions l on i.ItemCode = l.ItemCode 

I ended up having loops filling the blanks of inventory and transaction data.

Answer 1

I guess you want to use the sub select to generate arrays inside a table.

With ItemMasterData as (Select *, rand()*1000 as info_fields  from unnest(["IT0001","IT0002"]) ItemCode ),
Transactions as (Select ItemCode, Date from ItemMasterData,unnest(generate_date_array("2022-01-01","2022-01-03")) as Date),
Location as (Select ItemCode, rand()*100 as loc from unnest(["IT0001","IT0002","IT---"]) ItemCode,unnest(generate_array(1,5)) as Date)

select ItemCode , info_fields,
((Select array_agg(struct(date,"..." as other_fields)) from Transactions t where i.ItemCode=t.ItemCode group by ItemCode )) as Transactions,
((Select array_agg(struct(loc,"---" as another_fields)) from Location L where i.ItemCode=L.ItemCode group by ItemCode )) as Locations,

from
 ItemMasterData i

Another way is to generate a row number and combine the tables by this as well.

With ItemMasterData as (Select *, rand()*1000 as info_fields  from unnest(["IT0001","IT0002"]) ItemCode ),
Transactions as (Select ItemCode, Date from ItemMasterData,unnest(generate_date_array("2022-01-01","2022-01-03")) as Date),
Location as (Select ItemCode, rand()*100 as loc from unnest(["IT0001","IT0002","IT---"]) ItemCode,unnest(generate_array(1,5)) as Date)

select * #except(dummy_row),
from
 ItemMasterData

Left join
  (Select *, row_number() over (partition by ItemCode) as dummy_row from Transactions)
  using(ItemCode) 
full join
  (Select *,row_number() over (partition by ItemCode) as dummy_row from Location)
  using(ItemCode,dummy_row)

group by 1
order by 1,2