MySQL多表关联策略：如何查询指定作业的提交及分组提交参与用户

To retrieve all users who either directly submitted the assignment or were part of a group submission for a specific assignment ID, you need to combine user IDs from both the Submissions table (direct submitters) and the Group_submissions table (group participants linked to the assignment), then join with the Users table to get their full details.

Correct SQL Query #

Here's a clean approach using a CTE (Common Table Expression) to first collect all relevant user IDs, then fetch their user data:

WITH relevant_users AS (
    -- Get users who directly submitted the assignment
    SELECT user_id
    FROM Submissions
    WHERE assignment_id = 11 -- Replace with your target assignment ID
    
    UNION -- Automatically removes duplicate user IDs
    
    -- Get users who participated in group submissions for the assignment
    SELECT gs.user_id
    FROM Group_submissions gs
    INNER JOIN Submissions s 
        ON gs.submission_id = s.id
    WHERE s.assignment_id = 11 -- Same target assignment ID
)
SELECT u.*
FROM Users u
INNER JOIN relevant_users ru 
    ON u.id = ru.user_id;

Explanation #

1. CTE relevant_users:

   • The first part selects all user_id values from Submissions where the assignment_id matches your target. These are the direct submitters.
   • The second part joins Group_submissions with Submissions to filter group participants whose linked submission belongs to the target assignment.
   • UNION ensures that even if a user both submitted directly and was in a group, they only appear once in the result set.

2. Final Join:

   • We join the Users table with the relevant_users CTE to get all the user details (like id and name) for every user who meets the criteria.

Alternative (Without CTE) #

If your MySQL version doesn't support CTEs (most modern versions do), you can use a subquery instead:

SELECT u.*
FROM Users u
INNER JOIN (
    SELECT user_id FROM Submissions WHERE assignment_id = 11
    UNION
    SELECT gs.user_id FROM Group_submissions gs JOIN Submissions s ON gs.submission_id = s.id WHERE s.assignment_id = 11
) AS ru ON u.id = ru.user_id;

Why Your Original Queries Didn't Work #

• Your first query only fetched group members and used RIGHT JOINs which might include unintended rows, while excluding direct submitters.
• Your second query only fetched direct submitters, missing users who were part of group submissions but didn't submit directly.

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