How to do the join strategy in this case?

user_6014385 0
Viewed 50

This is the situation I'm dealing with:

I have 4 tables:

Users table:

+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
+----+-------+

Assignment table:

+----+-----------------+
| id | assignment_name |
+----+-----------------+
| 11 | name1           |
| 12 | name2           |
| 13 | name3           |
+----+-----------------+

Submissions:

+----+---------------+---------+
| id | assignment_id | user_id |
+----+---------------+---------+
|  1 |            11 |       3 |
|  2 |            12 |       1 |
|  3 |            11 |       2 |
+----+---------------+---------+

Group_submissions

+----+----------------+---------+
| id | submission_id | user_id  |
+----+----------------+---------+
|  1 |              1 |       1 |
|  2 |              1 |       2 |
|  3 |              3 |       1 |
+----+----------------+---------+

The submission table has an assignment_id to tell in which assignment the submission belongs to.

Also users can submit a group submission, where the one that does the submission goes to the submissions table, while the others go to the group_submissions table. That way it will be counted as one submission instead of being 2,3...N submission based on how many people where in the group.

How can i get the users that have submitted a submission or have participated in a group submission in a given assignment?

The result should return the user or users that have are in the submissions table or in the group_submissions table based on a assignment id

The result should look something likes this:

+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
+----+-------+

It should basically return the user table.

This is what i have tried so far:

This only gives me the users that aren't in the submissions table but are in the group_submission

select * from users u
right join group_submissions gs on u.id = gs.student_id
right join assignment_submissions ass on gs.submission_id = ass.id
inner join assignments a on a.id = ass.assignment_id
where a.id = number

This only gives me the one user that made the submission (in the submissions table)

select * from users u
right join assignment_submissions ass on u.id= ass.student_id
right join group_submissions gs on ass.id = gs.submission_id
inner join assignments a on a.id = ass.assignment_id
where a.id = number

What should my join strategy be here? Or maybe joins are not the right option here.

NOTE: This is a MySQL database.

mysql sql
2 Answers

You could use exists:

select u.*
from users u
where 
    exists (
        select 1 
        from submissions s 
        where s.user_id = u.id and s.assignment_id = ?
    )
    or exists (
        select 1 
        from group_submissions gs 
        inner join submissions s on s.id = gs.submission_id
        where gs.user_id = u.id and s.assignment_id = ?
    )
user_106767160

I am thinking a union could do it. Like this

select * from
(
    select u.*, assignment_id, 'assignment_submissions' as type from users u
    inner join assignment_submissions ass on u.id= ass.student_id
    union
    select u.*, assignment_id, 'group_submissions' from users u
    inner join group_submissions gs on u.id = gs.student_id
    inner join assignments a on a.id = ass.assignment_id
)a
where assignment_id = ?
user_66729230
Related