SQL内部联接超过两个表。

I can currently query the join of two tables on the equality of a foreign/primary key in the following way.

我现在可以通过以下方式查询两个表的连接是否相等。

 $result = mysql_query("SELECT * FROM `table1` 
                         INNER JOIN 
                       `table2` ON table1.primaryKey=table2.table1Id");

I'd like to extend this to multiple tables (all with the same foreign keys). I am trying the following code which is not returning anything. Can anyone point out what I'm doing wrong?

我想把这个扩展到多个表(所有的表都有相同的外键)。我正在尝试下面的代码，它没有返回任何东西。有人能指出我做错了什么吗?

 $result = mysql_query("SELECT * FROM `table1` 
                        INNER JOIN `table2` 
                        INNER JOIN table3 
                        ON table1.primaryKey=table2.table1Id=table3.table1Id");

9 个解决方案

#1

SELECT * 
FROM table1 INNER JOIN table2 ON 
     table1.primaryKey=table2.table1Id INNER JOIN 
     table3 ON table1.primaryKey=table3.table1Id

#2

Here is a general SQL query syntax to join three or more table. This SQL query should work in all major relation database e.g. MySQL, Oracle, Microsoft SQLServer, Sybase and PostgreSQL :

这里有一个通用的SQL查询语法，可以连接三个或多个表。这个SQL查询应该适用于所有主要的关系数据库，如MySQL、Oracle、Microsoft SQLServer、Sybase和PostgreSQL:

SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey
                                  join table3 ON table2.primarykey = table3.foreignkey

We first join table 1 and table 2 which produce a temporary table with combined data from table1 and table2, which is then joined to table3. This formula can be extended for more than 3 tables to N tables, You just need to make sure that SQL query should have N-1 join statement in order to join N tables. like for joining two tables we require 1 join statement and for joining 3 tables we need 2 join statement.

我们首先连接表1和表2，它生成一个临时表，其中包含表1和表2的组合数据，然后连接到表3。这个公式可以扩展到超过3个表到N个表，您只需要确保SQL查询应该有N-1连接语句来连接N个表。对于连接两个表，我们需要1个连接语句和连接3个表，我们需要2个连接语句。

#3

A possible solution:

一个可能的解决方案:

select Company.Company_Id,Company.Company_Name,
    Invoice_Details.Invoice_No, Product_Details.Price
from Company inner join Invoice_Details
    on Company.Company_Id=Invoice_Details.Company_Id
    inner join Product_Details
        on Invoice_Details.Invoice_No= Product_Details.Invoice_No
where Price='70000';

#4

The right syntax is like:

正确的语法是:

SELECT * FROM table1 INNER JOIN table2 ON table1.primaryKey = table2.ForeignKey
INNER JOIN table3 ON table3.primaryKey = table2.ForeignKey

Orthe last line joining table3 on table1 like:

在表1上的最后一行连接表1:

ON table3.ForeignKey= table1.PrimaryKey

#5

select * from Employee inner join [Order] 
On Employee.Employee_id=[Order].Employee_id
inner join Book 
On Book.Book_id=[Order].Book_id
inner join Book_Author
On Book_Author.Book_id=Book.Book_id
inner join Author
On Book_Author.Author_id=Author.Author_id;

#6

Please find inner join for more than 2 table here

请在这里找到超过2张表的内连接。

Here are 4 table name like

这里有4个表名。

1. Orders
2. 订单
3. Customers
4. 客户
5. Student
6. 学生
7. Lecturer
8. 讲师

So the SQL code would be:

所以SQL代码是:

select o.orderid, c.customername, l.lname, s.studadd, s.studmarks 
from orders o 
    inner join customers c on o.customrid = c.customerid 
    inner join lecturer l  on o.customrid = l.id 
    inner join student s   on o.customrid=s.studmarks;

#7

try this method given below, modify to suit your need.

尝试下面给出的方法，修改以适应您的需要。

SELECT
  employment_status.staff_type,
  COUNT(monthly_pay_register.age),
  monthly_pay_register.BASIC_SALARY,
  monthly_pay_register.TOTAL_MONTHLY_ALLOWANCES,
  monthly_pay_register.MONTHLY_GROSS,
  monthly_pay_register.TOTAL_MONTHLY_DEDUCTIONS,
  monthly_pay_register.MONTHLY_PAY
FROM 
  (monthly_pay_register INNER JOIN deduction_logs 
ON
  monthly_pay_register.employee_info_employee_no = deduction_logs.employee_no)
INNER JOIN
  employment_status ON deduction_logs.employee_no = employment_status.employee_no
WHERE
  monthly_pay_register.`YEAR`=2017
and 
  monthly_pay_register.`MONTH`='may'

#8

SELECT eb.n_EmpId,
   em.s_EmpName,
   deg.s_DesignationName,
   dm.s_DeptName
FROM tbl_EmployeeMaster em
 INNER JOIN tbl_DesignationMaster deg ON em.n_DesignationId=deg.n_DesignationId
 INNER JOIN tbl_DepartmentMaster dm ON dm.n_DeptId = em.n_DepartmentId
 INNER JOIN tbl_EmployeeBranch eb ON eb.n_BranchId = em.n_BranchId;

#9

Try this Here the syntax is

SELECT table1 .columnName, table3 .columnName 
   FROM table1 
     inner join table2 
          ON table1.primarykey = table2.foreignkey 
     inner join table3 
          ON table2.primarykey = table3.foreignkey

for example: Select SalesHeader.invoiceDate,ActualSales,DeptName,tblInvDepartment.DeptCode ,LocationCode from SalesDetail Inner Join SalesHeader on SalesDetail.InvoiceNo = SalesHeader.InvoiceNo inner join tblInvDepartment on tblInvDepartment.DeptCode = SalesDetail.DeptCode

例如:选择SalesHeader.invoiceDate ActualSales,DeptName tblInvDepartment。DeptCode,LocationCode来自SalesDetail内部连接SalesHeader。InvoiceNo = SalesHeader。在tblInvDepartment上加入tblInvDepartment。DeptCode = SalesDetail.DeptCode