MySQL查询异常：单条消息返回多行结果及JOIN语法问题求助

Hey there! Let's break down why you're seeing duplicate rows for single messages and fix this up.

The Root Cause #

Your original JOIN query only filters the friendship table where userone_ID = '13', but it doesn't actually link the Posts entries to the specific friendship relationship. This creates a Cartesian product: every post from user 13 or their friends gets paired with every friendship entry tied to user 13, resulting in duplicate rows for individual messages.

The Fix: Properly Link Posts to Friendships #

We need to update the JOIN condition to ensure each post is tied directly to the relevant friendship (i.e., the post is exchanged between user 13 and their specific friend). Here's the corrected query:

SELECT 
    P.msg, 
    P.userpost_ID, 
    P.created, 
    F.userone_ID, 
    F.usertwo_ID, 
    F.accepted, 
    U.fname, 
    U.lname 
FROM Posts P
JOIN friendship F 
    -- Ensure the post belongs to the friendship between user 13 and their friend
    ON (F.userone_ID = '13' AND P.userpost_ID IN ('13', F.usertwo_ID))
    OR (F.usertwo_ID = '13' AND P.userpost_ID IN ('13', F.userone_ID))
JOIN Users U 
    -- Pull the friend's name, no matter which side of the friendship they're listed on
    ON U.ID = IF(F.userone_ID = '13', F.usertwo_ID, F.userone_ID)
-- Optional: Filter out unaccepted friend requests to only show active conversations
WHERE F.accepted = 1;

Key Improvements: #

• Tightened JOIN Logic: We now explicitly connect each post to the friendship it belongs to, eliminating random cross-matches that caused duplicates.
• Flexible Friend Matching: The IF statement ensures we pull the correct friend's name even if user 13 is stored as usertwo_ID in some friendship entries.
• Clean Conversation Filter: The optional WHERE clause removes unapproved friend requests, so you only see messages from confirmed friends.

If you still spot duplicates, double-check if your Posts table has duplicate entries for the same message. You can add DISTINCT right after SELECT as a quick band-aid, but fixing the JOIN condition is the cleaner long-term solution.

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