SQL在多个字段中左侧连接多个表

问题描述：

我有10个表（table1，table2，table3等..），我想离开他们，并从每个表中获取几个字段。一些表格需要在多个字段上保持连接，例如， table1和table2需要在3个字段中加入。

这里是我尝试使用该脚本的样本，但我得到的错误（我掩盖了真正的表和字段名）：

SELECT table1.[Field1], 
     table1.[Field2], 
     table1.[Field3], 
     table1.[Field5],   
     table2.[Field1], 
     table2.[Field2], 
     table2.[Field3], 
     table3.[Field4], 
      table4.[Field1], 
     table4.[Field4], 
     table4.[Field5],   
     table5.[Field4], 
FROM table2 
LEFT JOIN table1 As a 
    on a.[Field1] = table2.[Field1] 
LEFT JOIN table1 As b 
    on b.[Field3] = table2.[Field3] 
LEFT JOIN table1 As c 
    on c.[Field2] = table2.[Field2] 
LEFT JOIN table4 As d 
    on table1.[Field5] = d.[Field4] 
LEFT JOIN table4 As e 
    on table1.[Field1] = e.[Field1] 
LEFT JOIN table5 
    on table4.[Field4] = table5.[Field4] 
LEFT JOIN table3 
    on table4.[Field4] = table3.[Field4];

任何“简单”的方法呢？

您加入tables.let我们知道你已经尝试过什么，你想要做什么的简单方法是什么意思？ –

该帖子现在被编辑。 – Jasu

答

尝试此查询。它可以帮助你

SELECT table1.[Field1], 
     table1.[Field2], 
     table1.[Field3], 
     table1.[Field5],   
     table2.[Field1], 
     table2.[Field2], 
     table2.[Field3], 
     table3.[Field4], 
     table4.[Field4], 
     table4.[Field5],   
     table5.[Field4], 
FROM table2 
LEFT JOIN table1 As a on a.[Field1] = table2.[Field1] 
and a.[Field3] = table2.[Field3] 
and a.[Field2] = table2.[Field2] 
LEFT JOIN table4 As d on table1.[Field5] = d.[Field4] 
and table1.[Field1] = d.[Field1] 
LEFT JOIN table5 on d.[Field4] = table5.[Field4] 
LEFT JOIN table3 on d.[Field4] = table3.[Field4];

答

use NORTHWND;
go

select Orders.OrderID,Employees.EmployeeID,Employees.FirstName + ' ' +
Employees.LastName as "Full Name",Customers.CompanyName as "Customer Company Name",
Customers.ContactName as "Customers Contact Name", RegionDescription,Products.ProductName,[Order Details].UnitPrice,Suppliers.CompanyName as "Supplier Company Name"
from Orders
left join Customers
on Orders.CustomerID=Customers.CustomerID
left join Employees
on Orders.EmployeeID=Employees.EmployeeID
left join EmployeeTerritories
on Employees.EmployeeID=EmployeeTerritories.EmployeeID
join Territories
on Territories.TerritoryID=EmployeeTerritories.TerritoryID
join Region
on region.RegionID=Territories.RegionID
join [Order Details]
on [Order Details].OrderID=Orders.OrderID
join Products
on Products.ProductID=[Order Details].ProductID
join Categories
on Categories.CategoryID=Products.CategoryID
join Suppliers
on Suppliers.SupplierID=products.SupplierID
where categories.CategoryID=1;

SQL在多个字段中左侧连接多个表

相关推荐