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

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')

CodePudding user response：

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');

CodePudding user response：

You should use inner join as below: (you can remove where clause if you want)

SELECT st.ItemNumber st.StoreNo st.StoreAddress st.StorePhoneNumber st.StoreType st.StoreOwner it.ItemWeight dt.ItemColor 
    FROM Store_Table st
LEFT JOIN Item_Table it
ON st.ItemNumber = it.ItemNumber
LEFT JOIN Description_Table dt
ON st.ItemNumber = dt.ItemNumber
WHERE st.StoreType = 'city' AND st.StoreOwner  NOT IN ('active', 'trial')

If you want all items with weight and color, you can change to inner join.