How to Efficiently Join Four Tables in MySQL to Avoid Duplicate Records?

Joining Multiple Tables in MySQL with INNER JOIN

When working with relational databases such as MySQL, joining tables allows you to retrieve data from multiple tables based on common columns. This tutorial guides you through the process of joining three tables: orders, products_pricing, and products.

Problem Statement

To retrieve products related to a specific user and include a hyperlink to each product, a fourth table, listings, needs to be joined. However, the current query returns duplicate records from the listings table.

Solution

To resolve this issue, modify the query as follows:

SELECT 
  p.id,
  p.name,
  l.url,
  o.user_id,
  o.pricing_id
FROM orders AS o
INNER JOIN products_pricing AS pp ON o.pricing_id  = pp.id
INNER JOIN products         AS  p ON pp.product_id = p.id
INNER JOIN listings         AS  l ON l.user_id = o.user_id
WHERE o.user_id ='7' 
  AND l.id = 233 
  AND l.url = 'test.com';

Explanation:

• l.id = 233 and l.url = 'test.com' are additional conditions that restrict the results to the specific listing with ID 233 and URL test.com.
• The INNER JOIN clauses ensure that only matching records from all four tables are returned.

Output

For the sample data provided, the updated query returns:

| ID | NAME | URL | USER_ID | PRICING_ID |

| 33 | test product | test.com | 7 | 37 |

This output includes the product ID, name, listing URL, user ID, and pricing ID for the specific user with user ID 7.

The above is the detailed content of How to Efficiently Join Four Tables in MySQL to Avoid Duplicate Records?. For more information, please follow other related articles on the PHP Chinese website!