HOW TO JOIN TABLES PROGRAMMATICALLY IN MAGENTO 2

Today we talk about How to join tables programmatically in Magento 2. Sometimes you need to get join collection with product collection data or category collection data or order collection or custom table collection. In order to make you do that with ease, the developer team from Magenest recommends the topic join tables in Magento2. Here we go.

How to join tables in SQL?

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL JOIN: Returns all records from both tables. ( we don’t use this much)

For this sample, we have the following tables:

Director_id	Name
1	Magenest director
2	Magenest
3	Son Tung
4	Cris
5	Magento2

Magenest_director

movie_id	name	description	rating	director_id
1	Harry Potter	Fantasy	1	1
2	Passenger	Action	2	2
3	Insidious	Horror	4	3
4	Pokemon	Anime	4	4

Magenest_movie

actor_id	name
1	Rowan
2	Tung
3	Satoshi
4	Magenest

Magenest_actor

movie_id	actor_id
1	1
2	2
2	3
3	3
4	4
4	1

Magenest_movie_actor

And their relationship:

 We want to get all records that have matching values from Magenest_movie, Magenest_director, and Magenest_actor. The query would be as follows:

SELECT `main_table`.name AS `movie`, `main_table`.description ,`main_table`.rating , `magenest_director`.`name` AS `director`, `magenest_actor`.name AS `actor` 

FROM `magenest_movie` AS `main_table` 

INNER JOIN `magenest_director` ON main_table.director_id=magenest_director.director_id 

INNER JOIN `magenest_movie_actor` ON main_table.movie_id=magenest_movie_actor.movie_id 

INNER JOIN `magenest_actor` ON magenest_actor.actor_id=magenest_movie_actor.actor_id;

Result:

movie	description	rating	director	Actor
Harry Potter	Fantasy	1	Magenest director	Rowan
Passenger	Action	2	Magenest	Tung
Passenger	Action	2	Magenest	Satoshi
Insidious	Horror	4	Son Tung	Satoshi
Pokemon	Anime	4	Cris	Magenest
Pokemon	Anime	4	Cris	Rowan

Next, get the all director’s names and the matched movie’s names:

SELECT `main_table`.name as `movie`,`magenest_director`.name AS `director` 

FROM `magenest_movie` AS `main_table` 

RIGHT  JOIN `magenest_director` ON main_table.director_id=magenest_director.director_id 

movie	director
Harry Potter	Magenest director
Passenger	Magenest
Insidious	Son Tung
Pokemon	Cris
Null	Magento2

By using LEFT JOIN in this case, the result is:

movie	director
Harry Potter	Magenest director
Passenger	Magenest
Insidious	Son Tung
Pokemon	Cris

Source: https://magenest.com/en/how-to-join-tables-programmatically-in-magento2/