英文:
I wish to append two query together , so that all fields in query one and query two appear in my union query
问题
查询两个表并附加结果
我正在尝试使用 UNION ALL 语句将[销售查询]和[生产查询]的结果合并在一起。然而，当我运行查询时，一些字段被省略了。如何附加这两个查询并确保结果集中包含所有字段？以下是我编写的 SQL 语句。
[生产查询]
SELECT Production.Date, Production.[Supplier/Demander], Item.Item, Item.Color, Production.[Opening BAL], Production.[In], Production.[Adj(+)], Production.[Total IN], Item.Grade, Item.TYPE
FROM Customers INNER JOIN ((Item INNER JOIN Production ON Item.[Item ID] = Production.[Item ID]) INNER JOIN Sales ON Item.[Item ID] = Sales.[Item ID]) ON Customers.Code = Sales.Code
GROUP BY Production.Date, Production.[Supplier/Demander], Item.Item, Item.Color, Production.[Opening BAL], Production.[In], Production.[Adj(+)], Production.[Total IN], Item.Grade, Item.TYPE;
[销售查询]
SELECT Sales.Date, Sales.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, Sales.Out, Sales.[Adj(-)], Sales.[Total Out], Item.Grade, Item.TYPE
FROM Customers INNER JOIN ((Item INNER JOIN Production ON Item.[Item ID] = Production.[Item ID]) INNER JOIN Sales ON Item.[Item ID] = Sales.[Item ID]) ON Customers.Code = Sales.Code
GROUP BY Sales.Date, Sales.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, Sales.Out, Sales.[Adj(-)], Sales.[Total Out], Item.Grade, Item.TYPE;
英文:
Querying two tables and appending the results
I am trying to combine the results from [Sales query] and [production query] using a union all statement. However, when I run the query, some of the fields are omitted. How can I append both queries and ensure that all the fields are included in the result set? Below is the SQL Statement i wrote.
[Production Query]
SELECT Production.Date, Production.[Supplier/Demander], Item.Item, Item.Color, Production.[Opening BAL], Production.[In], Production.[Adj(+)], Production.[Total IN], Item.Grade, Item.TYPE
FROM Customers INNER JOIN ((Item INNER JOIN Production ON Item.[Item ID] = Production.[Item ID]) INNER JOIN Sales ON Item.[Item ID] = Sales.[Item ID]) ON Customers.Code = Sales.Code
GROUP BY Production.Date, Production.[Supplier/Demander], Item.Item, Item.Color, Production.[Opening BAL], Production.[In], Production.[Adj(+)], Production.[Total IN], Item.Grade, Item.TYPE;
[Sales Query]
SELECT Sales.Date, Sales.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, Sales.Out, Sales.[Adj(-)], Sales.[Total Out], Item.Grade, Item.TYPE
FROM Customers INNER JOIN ((Item INNER JOIN Production ON Item.[Item ID] = Production.[Item ID]) INNER JOIN Sales ON Item.[Item ID] = Sales.[Item ID]) ON Customers.Code = Sales.Code
GROUP BY Sales.Date, Sales.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, Sales.Out, Sales.[Adj(-)], Sales.[Total Out], Item.Grade, Item.TYPE;
答案1
得分: 1
[生产查询]
选择 Production.Date, Production.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, Production.[Opening BAL], Production.[In], 0 作为 [Out], Production.[Adj(+)], Production.[Total IN], 0 作为 [Total Out], Item.Grade, Item.TYPE
[销售查询]
选择 Sales.Date, Sales.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, 0 作为 [Opening BAL], 0 作为 [In], Sales.Out, Sales.[Adj(-)], 0 作为 [Total In], Sales.[Total Out], Item.Grade, Item.TYPE
英文:
Count and data type of the fields of the two queries must be an exact match, like:
[Production Query]
SELECT Production.Date, Production.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, Production.[Opening BAL], Production.[In], 0 As [Out], Production.[Adj(+)], Production.[Total IN], 0 As [Total Out], Item.Grade, Item.TYPE
[Sales Query]
SELECT Sales.Date, Sales.[Supplier/Demander], Customers.Customer, Item.Item, Item.Color, 0 As [Opening BAL], 0 As [In], Sales.Out, Sales.[Adj(-)], 0 As [Total In], Sales.[Total Out], Item.Grade, Item.TYPE
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论