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

10 Answers

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

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 :

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.

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; 

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='60000'; 

-- tets changes

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:

ON table3.ForeignKey= table1.PrimaryKey 

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; 

Please find inner join for more than 2 table here

Here are 4 table name like

1. Orders
2. Customers
3. Student
4. Lecturer

So the SQL code would be:

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; 

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' 

select WucsName as WUCS_Name,Year,Tot_Households,Tot_Households,Tot_Male_Farmers from tbl_Gender INNER JOIN tblWUCS ON tbl_Gender.WUCS_id =tblWUCS .WucsId INNER JOIN tblYear ON tbl_Gender.YearID=tblYear.yearID 

There are 3 Tables 1. tbl_Gender 2. tblWUCS 3. tblYear

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