Required to Join 2 Tables With Their Fks in a 3Rd Table

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:

  1. Take a set of rows from table A
  2. Use the relationship between A and B to the related rows from table B
  3. Use the relationship between B and C to find the related rows in table C
  4. Use the relationship between C and D to find the related rows in table D


Related Topics



Leave a reply



Submit