求助：SQL查询结果出现重复customer_id问题的解决方法

Hey there! Let's break down why you're seeing duplicate customer_id values in your query output and how to fix it.

Why the Duplicates Happen #

Your query uses an INNER JOIN between customerinfo and familyrelation. If a single customer_id (mapped to fr.personid_two) has multiple entries in the familyrelation table (for example, one customer might be linked to multiple family members), the join will return a row for each matching relation entry—hence the duplicate customer_id values.

Solutions to Try #

1. Get Unique Row Combinations with DISTINCT

If you want to keep all unique combinations of user info and their relations (but eliminate exact duplicate rows), add DISTINCT to your SELECT clause:

SELECT DISTINCT ci.customer_id, ci.first_name, ci.user_gender, ci.customer_status, fr.relation 
FROM customerinfo ci 
INNER JOIN familyrelation fr ON fr.personid_two = ci.customer_id 
WHERE ci.customer_id IN (SELECT personid_two FROM familyrelation WHERE personid_one = 17) 
AND ci.csp_user_id = 5;

Note: This won't merge multiple relations for the same customer—it just removes identical rows. If a customer has different relation values, all those unique pairs will still show up.

2. Merge Multiple Relations into a Single Field

If you want one row per customer with all their relations grouped together, use an aggregate function to concatenate the relation values. The syntax varies by database:

• MySQL/MariaDB: Use GROUP_CONCAT

  SELECT ci.customer_id, ci.first_name, ci.user_gender, ci.customer_status, 
         GROUP_CONCAT(fr.relation SEPARATOR ', ') AS relations
  FROM customerinfo ci 
  INNER JOIN familyrelation fr ON fr.personid_two = ci.customer_id 
  WHERE ci.customer_id IN (SELECT personid_two FROM familyrelation WHERE personid_one = 17) 
  AND ci.csp_user_id = 5
  GROUP BY ci.customer_id, ci.first_name, ci.user_gender, ci.customer_status;
  

• PostgreSQL: Use STRING_AGG

  SELECT ci.customer_id, ci.first_name, ci.user_gender, ci.customer_status, 
         STRING_AGG(fr.relation, ', ') AS relations
  FROM customerinfo ci 
  INNER JOIN familyrelation fr ON fr.personid_two = ci.customer_id 
  WHERE ci.customer_id IN (SELECT personid_two FROM familyrelation WHERE personid_one = 17) 
  AND ci.csp_user_id = 5
  GROUP BY ci.customer_id, ci.first_name, ci.user_gender, ci.customer_status;
  

3. Pick a Single Relation per Customer

If you only need one relation entry per customer (and don't care which one), use a window function like ROW_NUMBER() to filter for just one row per customer_id:

SELECT customer_id, first_name, user_gender, customer_status, relation
FROM (
    SELECT ci.customer_id, ci.first_name, ci.user_gender, ci.customer_status, fr.relation,
           ROW_NUMBER() OVER (PARTITION BY ci.customer_id ORDER BY fr.relation) AS rn
    FROM customerinfo ci 
    INNER JOIN familyrelation fr ON fr.personid_two = ci.customer_id 
    WHERE ci.customer_id IN (SELECT personid_two FROM familyrelation WHERE personid_one = 17) 
    AND ci.csp_user_id = 5
) AS subquery
WHERE rn = 1;

Adjust the ORDER BY clause inside OVER() to pick which relation you want (e.g., ORDER BY fr.created_at DESC to get the most recent relation).

Verify the Root Cause #

First, confirm which customers have multiple entries in familyrelation with this quick check:

SELECT personid_two, COUNT(*) AS relation_count
FROM familyrelation
WHERE personid_two IN (SELECT personid_two FROM familyrelation WHERE personid_one = 17)
GROUP BY personid_two
HAVING COUNT(*) > 1;

This will show you exactly which customer_ids have duplicate relation records, so you can understand the source of the problem better.

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