英文:
Google BigQuery SQL using a JOIN to bring back data from 3 tables?
问题
需要在BigQuery中对SQL进行一些帮助，我有以下3个表格：
表格1
表格名称 = 'Store_Table'
ItemNumber
StoreNo
StoreAddress
StorePhoneNumber
StoreType
StoreOwner
表格2
表格名称 = 'Item_Table'
ItemNumber
ItemWeight
表格3
表格名称 = 'Description_Table'
ItemNumber
ItemColor
我想从表格1中获取所有数据，以及从表格2获取ItemWeight和从表格3获取ItemColor。共同字段是ItemNumber，所以我认为我可以使用它来连接（我假设是“左外连接”？）数据，但如何连接数据以从三个表格中获取字段，并且只让ItemNumber在结果中出现一次呢？
还有一个其他条件，就是必须包含一个where子句，例如：
where StoreType = 'city' and StoreOwner not in ('active','trial')。
有什么建议吗？谢谢。
对于只涉及两个表格的情况，您可以尝试以下查询：
SELECTTable1.ItemNumber AS ItemNumber,Table1.StoreNo AS StoreNo,Table1.StoreAddress AS StoreAddress,Table1.StorePhoneNumber AS StorePhoneNumber,Table1.StoreType AS StoreType,Table1.StoreOwner AS StoreOwner,Table2.ItemWeight AS ItemWeightFROM `Store_Table` AS Table1LEFT JOIN `Item_Table` AS Table2ON Table1.ItemNumber = Table2.ItemNumberWHERE Table1.StoreType = 'city' and Table1.StoreOwner not in ('active','trial');
这将从'Store_Table'和'Item_Table'中获取数据，通过ItemNumber字段连接它们，然后应用您的筛选条件。如果您还需要从'Description_Table'获取数据，您可以使用类似的方法，将其连接到上述查询中。
英文:
needed a bit of help with SQL in BigQuery, I have the 3 tables below:
Table1
*Table Name = ‘Store_Table’*ItemNumberStoreNoStoreAddressStorePhoneNumberStoreTypeStoreOwner
Table2
*Table Name = ‘Item_Table’*ItemNumberItemWeight
Table3
*Table Name = ‘Description_Table’*ItemNumberItemColor
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:
SELECTTable1.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` Table1LEFT OUTTER JOIN `Item_Table` Table2ON Table1.ItemNumber = Table2.ItemNumberStoreType = 'city' and StoreOwner not in ('active','trial')
答案1
得分: 0
以下是翻译好的代码部分：
SELECT *FROM Store_Table AS SLEFT OUTER JOIN Item_Table AS ION S.ItemNumber = I.ItemNumberLEFT OUTER JOIN Description AS DON I.ItemNumber = D.ItemNumberWHERE S.StoreType = 'city'AND S.StoreOwner not in ('active','trial');
英文:
You had a few syntax errors in your query but I'm guessing you want something like this:
SELECT *FROM Store_Table AS SLEFT OUTER JOIN Item_Table AS ION S.ItemNumber = I.ItemNumberLEFT OUTER JOIN Description AS DON I.ItemNumber = D.ItemNumberWHERE S.StoreType = 'city'AND S.StoreOwner not in ('active','trial');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论