multiple table join with where clause example

Sections

SectionMembers

MemberStatus

select s.section_id,  s.title,  s.description,  m.status
from Sections s left join SectionMembers sm  on s.section_id = sm.section_id
  and sm.memberid = 200 left join MemberStatus m  on sm.status_code = m.status_code
where s.section_ownerid = 100;

 results as shown below
ote: while your desired result shows that section_id=2 has a status of ActiveMember there is no way in your sample data to make this value link to section 2.
This query gives the result:

| SECTION_ID |  TITLE | DESCRIPTION |         STATUS |
------------------------------------------------------
|          1 | title1 |       desc1 |  PendingMember |
|          2 | title2 |       desc2 | MemberRejected |
|          3 | title3 |       desc3 | MemberRejected |
|          4 | title4 |       desc4 |   ActiveMember |
|          5 | title5 |       desc5 |         (null) |
|          6 | title6 |       desc6 |         (null) |

Difference between Self and Equi Join in SQL - INNER Join example MySQL

Difference between Self and Equi Join in SQL - INNER Join example MySQL

Main difference between Self Join and Equi Join is that, In Self Join we join one table to itself rather than joining two tables. Both Self Join and Equi Join are types of INNER Join in SQL but there is subtle difference between two. Any INNER Join with equal as join predicate is known as Equi Join. SQL Joins are fundamental concept of SQL similar to correlated and noncorrelated subqueries or using group by clause and good understanding of various types of SQL join is must for any programmer. By the way If you have written INNER join using where clause than using comparison operator as = will be known as equi join. Equi join or Self join are not a formal join or part of syntax, instead they are just popular way to refer certain join examples. One of the best example of Self Join, I have seen in SQL Interview questions is "How do you find all Employees who are Managers in Employee table", which is commonly asked along with another popular question how to find second highest salary of employee or questions related to join three tables in one sql query. In this SQL tutorial we will learn self join by example while solving this SQL query.

Self Join Example in SQL

In order to solve this query let's first see schema and data of Employee table.

mysql> select * from employee;
+--------+----------+---------+--------+--------+
| emp_id | emp_name | dept_id | salary | mgr_id |
+--------+----------+---------+--------+--------+
|    103 | Jack     |       2 |   1400 |    104 |
|    104 | John     |       2 |   1450 |    104 |
|    105 | Johnny   |       3 |   1050 |    104 |
|    108 | Alan     |       3 |   1150 |    104 |
|    106 | Virat    |       4 |    850 |    105 |
|    107 | Vina     |       4 |    700 |    105 |
|    109 | joya     |       4 |    700 |    105 |
+--------+----------+---------+--------+--------+
7 rows in set (0.00 sec)

In above  table all employees who are managers has there emp_id as mgr_id in other employees and by using SELF JOIN  i.e. join two instance of employee table and comparing, we can find all employees who are managers. Here is the SELECT query example using self join :

mysql> select distinct e.emp_id, e.emp_name from employee e join employee m on e.emp_id=m.mgr_id;
+--------+----------+
| emp_id | emp_name |
+--------+----------+
|    104 | John     |
|    105 | Johnny   |
+--------+----------+
2 rows in set (0.00 sec)

In this example of Self Join, we have joined employee table to itself by using two table aliases e and m. We have also used distinct keyword to remove duplicates here. You can also say this is an example of EQUI JOIN because in join predicate we have used = or equal condition. In fact this one is example of INNER Join, SELF Join and EQUI Join at same time.

Self Join vs Equi Join

In short major difference between Self Join and Equi Join in SQL is that Self Join requires only one table while most of Equi join is condition used in join predicate. Since Equi Join is based on condition for comparison, it can occur in any INNER, OUTER or SELF join in SQL.

That’s all on difference between Self Join and Equi Join in SQL. Self Join is one of the important technique to solve many SQL query related problem where two columns of table contains same type of data e.g. here emp_id and dept_id are essentially same data.