SQL Inner Join More Than Two Tables

SQL Inner join more than two tables

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

Perform an INNER JOIN with more than 2 tables in MySQL

You want two joins. The syntax is:

SELECT *
FROM payers p
INNER JOIN discounts d ON d.id = p.id
INNER JOIN items     i ON i.id = p.id

Side notes:

• you did not show your actual schema, so this uses the join conditions described in your attempt; you might need to review that

• table aliases make the query shorter to write and easier to read

• SELECT * is generally not good practice; instead, I would recommend enumerating the columns you want in the SELECT clause, and properly aliasing conflicting columns names, if any (here, all three tables have a column called id, which would cause ambiguity in the resultset)

sql inner join on more than 2 tables and aggregate function

you have to use group by as you used aggregate funtion

   select d.departmentid, d.name, 
count(distinct(sg.personid)) 
from department d inner join course c on c.departmentid = d.departmentid inner join student_grade sg on sg.courseid = c.courseid;
group by  d.departmentid, d.name

Inner join with Count, multiple tables

As I don't succeed to get a result with Gordon Linoff answer, I tried an other way with LEFT OUTER JOIN.

Thanks to this post :
How to get a group where the count is zero

I managed to get a correct result with void relations (for example, an item Table2 has no reference in Table3), but now I got an incorrect result when I join more than 2 tables.

I guess it's a problem from my request...

Currently :

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID
GROUP BY Table1.UID, Table1.Name

gives me a correct result (only two tables related), but :

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2, COUNT(Table3.UID) AS CountTable3
FROM (Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID)
LEFT OUTER JOIN Table3 ON Table3.FK_Table2 = Table2.UID
GROUP BY Table1.UID, Table1.Name

gives me an incorrect result for CountTable2, which appears to be more than expected. CountTable3 is correct.

EDIT :

I finally figure how to make it works, according to my research and the hint from Gordon Linoff with aggregation before joining.

I start by counting the deepest table inside the table above, then join, and so on. At every step, I select the essentials informations to keep : UID, FK_AboveTable, Count, Sums from deepest table.

Final code :

SELECT Table1.UID, Table1.Name, COUNT(Table2.UID) AS TotalTable2, SUM(CountTable3) AS TotalTable3, SUM(CountTable4_2) AS TotalTable4
FROM Table1 LEFT OUTER JOIN (
    SELECT Table2.UID, Table2.FK_Table1, COUNT(Table3.UID) AS CountTable3, 
    SUM(CountTable4) AS CountTable4_2
    FROM Table2 LEFT OUTER JOIN (
        SELECT Table3.UID, Table3.FK_Table1, COUNT(Table4.UID) AS CountTable4
        FROM Port LEFT OUTER JOIN
            Table4 ON Table4.FK_Table3 = Table3.UID
            GROUP BY Table3.UID, Table3.FK_Table2
        ) Table3 ON Table3.FK_Table2 = Table2.UID
        GROUP BY Table2.UID, Table2.FK_Table1
    ) Table2 ON Table2.FK_Table1= Table1.UID
GROUP BY Table1.UID, Table1.Name ORDER BY Table1.Name DESC

Note that void count from deepest table appears are void and no 0 (for example, if there is one item in Table1, none related in Table2, the count will be 0 for Table2, void for Table3 and Table4).

I assume this might be upgraded, but for now it solves my issue, and allows me to add as many tables as I need.

SQL Join: Are selects between more than 2 tables still joins?

Inner joins and outer joins are perfectly reasonable to use with more than 2 tables.

Inner joins force the result to display only data that has whatever row you joined on, whereas outer joins display all data no matter what.

Let us say you wanted to join 4 tables together...

select * from testtable
inner join testable2 on col1 = othercolumn
inner join testable3 on col2 = othercolumn
leftjoin testable4 on col3 = othercolumn

In this case, it would return only results that existed in the inner joins, but the result would not have to exist in the outside/left join. You are forcing testtables 2 & 3 to have a value on what you are joining on.. it cannot be null.

The left join does not care if the value is null, and will show results anyway.
I hope this helps some... Basically.. if you inner join on a value, and it can possibly be null, then the entire query will show blank. This is the scenario you would use an outter join.. you are not forcing the value to exist.

Access-SQL: Inner Join with multiple tables

If you are writing a query against an Access database backend, you need to use the following join syntax:

select
  t1.c1
, t2.c2
, t3.c3
, t4.c4
from ((t1
inner join t2 on t1.something = t2.something)
inner join t3 on t2.something = t3.something)
inner join t4 on t3.something = t4.something

The table and column names aren't important here, but the placement of the parentheses is. Basically, you need to have n - 2 left parentheses after the from clause and one right parenthesis before the start of each new join clause except for the first, where n is the number of tables being joined together.

The reason is that Access's join syntax supports joining only two tables at a time, so if you need to join more than two you need to enclose the extra ones in parentheses.

Inner join more than 2 tables in a single database

You need an on clause for each inner join. e.g.

select * from a 
inner join b on a.id = b.id 
inner join c on b.id = c.id

SQL inner join multiple tables with one query

Try the following:

SELECT
c.testID
FROM c 
INNER JOIN b ON c.test IS NOT NULL AND b.r_ID=c.testID 
INNER JOIN a ON a.id=b.r_ID;

I changed the order of the joins and conditions so that the first statement to be evaluated is c.test IS NOT NULL

Disclaimer: You should use the explain command in order to see the execution.
I'm pretty sure that even the minor change I just did might have no difference due to the MySql optimizer that work on all queries.

See the MySQL Documentation: Optimizing Queries with EXPLAIN

Three queries Compared

Have a look at the following fiddle:
https://www.db-fiddle.com/f/fXsT8oMzJ1H31FwMHrxR3u/0

I ran three different queries and in the end, MySQL optimized and ran them the same way.

Three Queries:

EXPLAIN SELECT
c.testID
FROM c 
INNER JOIN b ON c.test IS NOT NULL AND b.r_ID=c.testID 
INNER JOIN a ON a.id=b.r_ID;


EXPLAIN SELECT c.testID
FROM a
INNER JOIN b ON a.id = b.r_id
INNER JOIN c ON b.r_ID = c.testID AND c.test IS NOT NULL;

EXPLAIN SELECT
c.testID
FROM a
INNER JOIN b ON a.id=b.r_ID
INNER JOIN c ON b.r_ID=c.testID
WHERE c.test IS NOT NULL;

---

Related Topics