Google BigQuery SQL using a JOIN to bring back data from 3 tables?

Question

needed a bit of help with SQL in BigQuery, I have the 3 tables below:

Table1

*Table Name = ‘Store_Table’*
ItemNumber
StoreNo
StoreAddress
StorePhoneNumber
StoreType
StoreOwner

Table2

*Table Name = ‘Item_Table’*
ItemNumber
ItemWeight

Table3

*Table Name = ‘Description_Table’*
ItemNumber
ItemColor

I want to bring back all the data from table 1 plus ItemWeight from table 2 and ItemColor from table 3. The common field is ItemNumber so I presume I use that to JOIN (I presume a ‘left outer join’?) the data but how do I join the data to bring back fields from the three tables plus only have ItemNumber appear once in the result?

There is also one other criteria, which is it has to have a where clause included, example:

where StoreType = 'city' and StoreOwner not in ('active','trial').

Any ideas? Thanks.

Tried the below for just 2 tables:

SELECT
Table1.gpid AS field1,
Table1.ItemNumber AS field2,
Table1.StoreNo  AS field3,
Table1.StoreAddress  AS field4,
Table1.StorePhoneNumber  AS field5,
Table1.StoreType  AS field6,
Table1.StoreOwner  AS field7,
Table2.ItemNumber AS field8,
Table2.ItemWeight  AS field9,
FROM `Store_Table` Table1
LEFT OUTTER JOIN `Item_Table` Table2
ON Table1.ItemNumber = Table2.ItemNumber
StoreType = 'city' and StoreOwner not in ('active','trial')

Answer 1

You had a few syntax errors in your query but I'm guessing you want something like this:

SELECT *
FROM Store_Table AS S
LEFT OUTER JOIN Item_Table AS I
ON S.ItemNumber = I.ItemNumber
LEFT OUTER JOIN Description AS D
ON I.ItemNumber = D.ItemNumber
WHERE S.StoreType = 'city'
AND S.StoreOwner not in ('active','trial');