How Can I Join the Same Table Twice in a MySQL Query?

Joining on the Same Table Twice in MySQL

MySQL's join operation allows you to merge rows from multiple tables based on a common key. In certain scenarios, it may be necessary to join a table with itself, twice. This technique is commonly used to display multiple columns from the same table in the query results.

Use Case

Consider a database with two tables: domains and reviews. The domains table contains domain IDs (dom_id) and domain names (dom_url). The reviews table contains domain IDs for the "from" and "to" domains involved in a review (rev_dom_from, rev_dom_to).

To display both domain names for each review, it is necessary to join the domains table twice.

Query Structure

To achieve this, use a LEFT JOIN for each desired join. In this example, alias the domains table as toD and fromD for clarity.

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;

Explanation

• Table Selection: The query starts by selecting rows from the reviews table, aliasing it as "rvw."
• First LEFT JOIN: The first LEFT JOIN connects the domains table (aliased as toD) to the reviews table based on the "To" domain ID (toD.dom_id = rvw.rev_dom_for).
• Second LEFT JOIN: The second LEFT JOIN brings in the domains table a second time (aliased as fromD) and connects it to the reviews table based on the "From" domain ID (fromD.dom_id = rvw.rev_dom_from).
• SELECT Clause: The SELECT clause specifies that the query should return the dom_url field from the toD alias as ToURL and the dom_url field from the fromD alias as FromUrl. It also includes all columns (rvw.*) from the reviews table in the results.

The above is the detailed content of How Can I Join the Same Table Twice in a MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!