How to make two joins between two tables in MySQL such that they are interlinked to each other?
How to proceed towards the solution
Two joins can be formed between two columns by using Table aliases. As the question specifies, that one join is to be formed between the employee
and the branch
table, and another join needs to be formed between the branch
and the employee
table. The little bit tricky part of these types of joins is the relation specified after the ON
keyword that joins the two tables.
As @philipxy writes in a comment to this question:
Constraints (including FKs & PKs) need not hold, be declared or be known in order to record or query. Joins are binary, the left table is the result of any previous joins in a series without parentheses. Except for output column order, inner & cross joins have no direction, t join u on c is u join t on c.
So according to the comment, we would form a join between employee
and branch
and another join between employee
and an alias of branch table called branch2
. The common confusion here is that most people(including me earlier) think that there is a "direction" of joins, the thing that philipxy covers in his aforementioned comment.
The solution to the problem
You can write a SQL query which queries the first_name
, last_name
and branch_id
from the employee
table and the branch_name
from the branch
table and forms a join between the two tables on the basis of branch_id
. You have to query the mgr_id
from the alias of the branch table called branch2
; you have to query the first_name
and the last_name
of the branch managers from the employee table. You can easily join the employee and the branch table on the basis of emp_id
such that the mgr_id
=emp_id
.
You can finally write the SQL query for the problem like this:
SELECT employee.first_name, employee.last_name, employee.branch_id,
branch.branch_name,
branch2.mgr_id, employee.first_name AS manager_first_name, employee.last_name AS manager_last_name
FROM employee
JOIN branch ON employee.branch_id=branch.branch_id
JOIN branch branch2 ON branch2.mgr_id=employee.emp_id;
Extra information
The above mentioned query would return this:
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| first_name | last_name | branch_id | branch_name | mgr_id | manager_first_name | manager_last_name |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| David | Wallace | 1 | Corporate | 100 | David | Wallace |
| Michael | Scott | 2 | Scranton | 102 | Michael | Scott |
| Josh | Porter | 3 | Stamford | 106 | Josh | Porter |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
These results might look useless as we have formed an INNER JOIN
between the tables so it just returns us the name of the employees who are "managers" of a specific branch. If you form a LEFT JOIN
between the tables instead of an INNER JOIN
you would get results like this:
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| first_name | last_name | branch_id | branch_name | mgr_id | manager_first_name | manager_last_name |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| David | Wallace | 1 | Corporate | 100 | David | Wallace |
| Jan | Levinson | 1 | Corporate | NULL | Jan | Levinson |
| Michael | Scott | 2 | Scranton | 102 | Michael | Scott |
| Angela | Martin | 2 | Scranton | NULL | Angela | Martin |
| Kelly | Kapoor | 2 | Scranton | NULL | Kelly | Kapoor |
| Stanley | Hudson | 2 | Scranton | NULL | Stanley | Hudson |
| Josh | Porter | 3 | Stamford | 106 | Josh | Porter |
| Andy | Bernard | 3 | Stamford | NULL | Andy | Bernard |
| Jim | Halpert | 3 | Stamford | NULL | Jim | Halpert |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
These results were not as expected as the employees who are not managers of any branch just have a mgr_id
with NULL
value whereas the branch that they word in actually has a manager. With the mgr_id
being NULL, the manager_first_name
and manager_last_name
have unexpected results too.
The above occurs because we cannot have the same manager for two employees because mgr_id
can not be the same accross rows as it is the emp_id
which is the PRIMARY KEY of the employee
table.
Credits
- @philpxy 's comments on this question
Connecting two tables using third table with foreign key in mySQL
If you want to search by user's name you must join all 3 tables:
select u.*, s.*
from users u
inner join connection c on c.userID = u.userID
inner join schedule s on s.actID = c.actID
where u.username = ?
If you want to search by user's id you must join only 2 tables:
select c.userID, s.*
from connection c inner join schedule s
on s.actID = c.actID
where c.userID = ?
If a user does not have any activities but you want in the results 1 row, with no activity then use left joins:
select u.*, s.*
from users u
left join connection c on c.userID = u.userID
left join schedule s on s.actID = c.actID
where u.username = ?
Join 3 tables with different FKs
The proper syntax is something like this:
SELECT a.Article_Name
FROM Articles a INNER JOIN
Author au
ON a.Author_ID = au.Author_ID INNER JOIN
Location l
ON l.Location_ID = au.Location_Id
WHERE au.First_Name = 'Sam' AND l.City <> 'Detroit';
Note the use of table aliases make the query easier to write and to read. Also, you need conditions connecting the two tables for the ON
clauses.
SQL select with three tables and foreign keys
You must join all 3 tables on their related columns:
SELECT p.p_name, f.f_start, f.f_end
FROM person p
INNER JOIN affect a ON a.fk_person = p.p_id
INNER JOIN field f ON f.f_id = a.fk_field;
Depending on your requirement you may need LEFT instead of INNER joins, but for this sample data the INNER joins will do.
join a to c where table a and table c has only table b as relation
In this case you need to use 2 inner joins
to get data from tablec
like following.
select a.*,c.coltoselect
from tablea a
inner join tableb b on a.abcommoncolumn=b.abcommoncolumn
inner join tablec c on b.bccommoncolumn=c.bccommoncolumn
Join two tables through third one with two foreign keys
If you want to know the deposits in which a bank does not participate:
1- Add a related_name to deposit and bank:
class DepositProposal(models.Model):
percent = models.FloatField()
deposit = models.ForeignKey(Deposit, related_name = "proposals")
bank = models.ForeignKey(Bank, related_name = "proposals")
2- Get the bank that you want to check:
bank = Bank.objects.first() #For example, the first bank
3- Get the queryset:
deposits = Deposit.objects.exclude(proposals__bank = bank)
EDIT:
If you want to know the banks that not participate on a specific deposit:
deposit = Deposit.objects.first() #For example, the first deposit
banks = Bank.objects.exclude(proposals__deposit = deposit)
How to understand the four tables join query
When making multiple joins, not all of the tables need to relate to a single table, there just needs to be a chain of relationships that link each additional table to one of the previous tables.
Let's look at a simpler example where we have 4 tables: A, B, C, and D.
- A is related to B
- B is related to C
- C is related to D
So, tables C and D aren't directly related to A, but through the chain of relationships we can find the correct rows across all the tables with a process something like this:
- Take a set of rows from table A
- Use the relationship between A and B to the related rows from table B
- Use the relationship between B and C to find the related rows in table C
- Use the relationship between C and D to find the related rows in table D
Related Topics
How to Create a Sequence in MySQL
SQL Server - in Clause With a Declared Variable
Db2 Comma Separated Output by Groups
SQL to Json - Array of Objects to Array of Values in SQL 2016
How to Get the Difference in Years from Two Different Dates
When No 'Order By' Is Specified, What Order Does a Query Choose For Your Record Set
How to Store Only Time; Not Date and Time
Parse Comma-Separated String to Make in List of Strings in the Where Clause
How to Count Unique Items in Field in Access Query
Fastest Way to Perform Nested Bulk Inserts With Scope_Identity() Usage
Oracle SQL - How to Retrieve Highest 5 Values of a Column
Pass Multiple Values in Single Parameter
Unrecognized Name: Employees At [9:8]
Using Stored Procedure in Classical Asp .. Execute and Get Results