How to Self-Join a Table in MySQL to Retrieve Data from Multiple Rows?

How to Join the Same Table Multiple Times in MySQL

In scenarios where you need to relate records from the same table multiple times within a query, MySQL offers the ability to join a table with itself. This allows you to retrieve data from different rows of the same table, effectively creating a new relationship between the rows.

To illustrate this concept, consider the following scenario:

You have two tables: "domains" and "reviews". The "domains" table stores domain IDs and domain names, while the "reviews" table contains review data and references domain names for both the sender (rev_dom_from) and recipient (rev_dom_for).

Your task is to display both domain names on a webpage. While you can easily display one domain name using a standard LEFT JOIN, the question arises: how do you retrieve the second domain name from the "rev_dom_from" column?

The solution lies in utilizing multiple joins. By joining the "domains" table multiple times, you can establish different relationships between the reviews and the corresponding domain names:

SELECT toD.dom_url AS ToURL, 
    fromD.dom_url AS FromUrl, 
    rvw.*

FROM reviews AS rvw

LEFT JOIN domain AS toD 
    ON toD.Dom_ID = rvw.rev_dom_for

LEFT JOIN domain AS fromD 
    ON fromD.Dom_ID = rvw.rev_dom_from

In this query:

• toD is the alias for the first instance of the joined "domains" table, representing the "To" domain name.
• fromD is the alias for the second instance of the joined "domains" table, representing the "From" domain name.

By aliasing the joined tables, you can differentiate between the two instances and retrieve the desired data. This technique of joining the same table multiple times is commonly referred to as "self-joining" and can be applied in various scenarios where you need to establish multiple relationships within a single table.

The above is the detailed content of How to Self-Join a Table in MySQL to Retrieve Data from Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!