使用DISTINCT仍返回重复行的SQL多表查询问题

Hey there! Let's work through that duplicate row problem in your SQL query. I took a look at your code, and the issue is likely coming from multiple matching rows in one or more of the joined tables (like TextEntries, userfieldxrefs, or PackageList). When you join these tables, each matching row in the child tables will create a duplicate row for the parent objects entry—even with DISTINCT, since other columns (like the text entry or field value) might differ between those duplicates.

Here are a few straightforward fixes depending on what you need:

1. Group rows to eliminate duplicates (most common case) #

If each objectnumber should have only one value for the text entry (type 9) and field value (ID 25), use GROUP BY with an aggregate function like MAX() to pick the non-null value for each group. This works because MAX() ignores NULLs, so it'll grab the valid value if it exists for that object:

SELECT 
    o.objectnumber, 
    g.locale, 
    g.locus, 
    g.excavation, 
    g.mapreferencenumber, 
    MAX(CASE WHEN t.texttypeid = '9' THEN t.textentry END) AS text_entry_type9,
    MAX(CASE WHEN f.userfieldid = '25' THEN f.fieldvalue END) AS field_value_25
FROM objects o 
INNER JOIN TextEntries t ON t.id = o.objectid 
INNER JOIN ObjGeography g ON g.ObjectID = o.objectid 
INNER JOIN userfieldxrefs f ON f.id = o.objectid 
INNER JOIN PackageList pl ON o.objectID = pl.ID 
INNER JOIN Packages p ON pl.PackageID = p.PackageID 
WHERE p.packageid = '8502' 
GROUP BY 
    o.objectnumber, 
    g.locale, 
    g.locus, 
    g.excavation, 
    g.mapreferencenumber
ORDER BY g.mapreferencenumber ASC;

I also changed like to = for the packageid check—since you're looking for an exact match, like is unnecessary here and can slow down the query a bit.

2. Combine multiple values into one string (if you need all matches) #

If an object might have multiple valid text entries or field values, use a string aggregation function to merge them into a single column instead of creating duplicate rows. The function name varies by database:

• SQL Server: STRING_AGG()
• MySQL: GROUP_CONCAT()
• PostgreSQL: STRING_AGG()

Here's what it looks like for SQL Server:

SELECT 
    o.objectnumber, 
    g.locale, 
    g.locus, 
    g.excavation, 
    g.mapreferencenumber, 
    STRING_AGG(CASE WHEN t.texttypeid = '9' THEN t.textentry END, ', ') AS text_entries_type9,
    STRING_AGG(CASE WHEN f.userfieldid = '25' THEN f.fieldvalue END, ', ') AS field_values_25
FROM objects o 
INNER JOIN TextEntries t ON t.id = o.objectid 
INNER JOIN ObjGeography g ON g.ObjectID = o.objectid 
INNER JOIN userfieldxrefs f ON f.id = o.objectid 
INNER JOIN PackageList pl ON o.objectID = pl.ID 
INNER JOIN Packages p ON pl.PackageID = p.PackageID 
WHERE p.packageid = '8502' 
GROUP BY 
    o.objectnumber, 
    g.locale, 
    g.locus, 
    g.excavation, 
    g.mapreferencenumber
ORDER BY g.mapreferencenumber ASC;

This will list all matching values separated by commas, keeping each object as a single row.

3. Use LEFT JOIN if you need to keep objects without matches #

If some objects in package 8502 don't have a type 9 text entry or user field 25, your current inner joins will exclude them entirely. Switching to LEFT JOIN (and moving the type/ID checks to the join condition) will keep those objects in the results with NULL for the missing fields:

SELECT 
    o.objectnumber, 
    g.locale, 
    g.locus, 
    g.excavation, 
    g.mapreferencenumber, 
    MAX(CASE WHEN t.texttypeid = '9' THEN t.textentry END) AS text_entry_type9,
    MAX(CASE WHEN f.userfieldid = '25' THEN f.fieldvalue END) AS field_value_25
FROM objects o 
INNER JOIN ObjGeography g ON g.ObjectID = o.objectid 
INNER JOIN PackageList pl ON o.objectID = pl.ID 
INNER JOIN Packages p ON pl.PackageID = p.PackageID 
LEFT JOIN TextEntries t ON t.id = o.objectid AND t.texttypeid = '9'
LEFT JOIN userfieldxrefs f ON f.id = o.objectid AND f.userfieldid = '25'
WHERE p.packageid = '8502' 
GROUP BY 
    o.objectnumber, 
    g.locale, 
    g.locus, 
    g.excavation, 
    g.mapreferencenumber
ORDER BY g.mapreferencenumber ASC;

This is cleaner because we only join the relevant rows from TextEntries and userfieldxrefs instead of joining all rows and filtering later.

Pick the approach that fits your data and reporting needs—let me know if you need help adapting this to your specific database!

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