使用MySQL JOIN查询5张表出现重复数据：单条记录返回8条重复行

Hey there! It’s super common to run into duplicate rows when joining multiple tables—let’s break down why you’re getting 8 identical records per target entry and how to fix it.

Why This Happens #

Most likely, you’re dealing with one-to-many relationships across your joined tables. For example:

• If Table A has 1 record that matches 2 records in Table B
• Table B’s records each match 2 in Table C
• Table C’s records each match 2 in Table D
  That’s 2×2×2=8 combinations, which explains the 8 duplicate rows for a single target record. Each join multiplies the number of rows based on how many matches exist in the related table.

Step-by-Step Fixes #

1. Verify Your Join Conditions First

Double-check that each JOIN uses precise, correct foreign key relationships. Loose or incorrect conditions can accidentally create extra matches.

• Example of a correct join:

  JOIN table2 t2 ON t1.id = t2.table1_id -- Uses the explicit foreign key
  

• Avoid vague joins like ON t1.some_column = t2.some_column if that column isn’t meant to link records uniquely.

2. Use DISTINCT to Filter Exact Duplicates

If you don’t need the multiple combinations from one-to-many relationships, add DISTINCT right after SELECT to remove identical rows:

SELECT DISTINCT t1.*, t2.column_name, t3.column_name
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
JOIN table3 t3 ON t1.id = t3.table1_id
JOIN table4 t4 ON t1.id = t4.table1_id
JOIN table5 t5 ON t1.id = t5.table1_id;

Note: This only works if the duplicate rows are completely identical. If your SELECT includes unique fields (like auto-increment IDs from child tables), DISTINCT won’t help—you’ll need to fix the join logic instead.

3. Group Rows with GROUP BY

If you need to aggregate data from child tables (instead of showing every match), use GROUP BY on the unique identifier of your target record (like table1.id), paired with aggregate functions:

SELECT 
  t1.id, 
  t1.target_column,
  MAX(t2.some_value) AS max_value, -- Get the highest value from table2
  GROUP_CONCAT(t3.info SEPARATOR ', ') AS combined_info -- Merge multiple table3 entries
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
JOIN table3 t3 ON t1.id = t3.table1_id
JOIN table4 t4 ON t1.id = t4.table1_id
JOIN table5 t5 ON t1.id = t5.table1_id
GROUP BY t1.id, t1.target_column; -- Include all non-aggregated fields here

Pro tip: If MySQL throws an error about ONLY_FULL_GROUP_BY, make sure your GROUP BY includes every column in your SELECT that isn’t wrapped in an aggregate function.

4. Replace Joins with EXISTS for Existence Checks

If you only need to confirm that related records exist (not retrieve their data), use EXISTS instead of JOIN—this avoids multiplying rows entirely:

SELECT t1.*
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.table1_id = t1.id)
  AND EXISTS (SELECT 1 FROM table3 t3 WHERE t3.table1_id = t1.id)
  AND EXISTS (SELECT 1 FROM table4 t4 WHERE t4.table1_id = t1.id)
  AND EXISTS (SELECT 1 FROM table5 t5 WHERE t5.table1_id = t1.id);

Troubleshooting Tip #

To pinpoint which table is causing the duplicates, add joins one at a time and run the query after each step. For example:

1. Start with SELECT * FROM table1 (no duplicates)
2. Add JOIN table2—check if rows multiply
3. Continue adding tables until you see duplicates. That last table is the one with the one-to-many relationship causing the issue.

内容的提问来源于stack exchange，提问作者yussuf mussa