SQL query - dynamic sub query

Question

I am having trouble trying to create a query to:

Select all the students who have not completed all peer review's for a particular week.

background: Each week, every student must peer review their peers in the same group.

Each group can be a different size, which is the problem I am having.

this is my current test data:

peer review table

student table

Table 1: peer review table
Table 2: student table.

This is my inital query, groups all the students based on the amount of peer review's they've made. I now need to to check if the count(*) is less than the size of the group for each student :

SELECT * 
FROM peerreview
RIGHT JOIN student 
ON student. studentID = peerreview.reviewer
WHERE week = 11
GROUP BY studentID
HAVING Count(*) < ????

The 'WHERE week = 11' bit effectively turns your RIGHT JOIN into an INNER JOIN. — Matt Gibson
– Matt Gibson, Commented May 28, 2012 at 9:55
just give column names of both d tables and common id b/w them.\ — Addicted
– Addicted, Commented May 28, 2012 at 9:57

Romil Kumar Jain · Accepted Answer · 2012-05-28 10:40:16Z

2

Following query will return the student which has reviewed all the students in same group.

 SELECT a.reviewer, 
       a.groupid 
FROM   (SELECT student2.studentID AS reviewer, 
               student1.groupid, 
               Count(*)           AS cnt 
        FROM   student student1 
               INNER JOIN peerreview 
                       ON student1.studentID = peerreview.reviewee 
               INNER JOIN STUDENT STUDENT2 
                       ON student2.studentID = peerreview.reviewer 
        WHERE  student2.groupid = student2.groupid 
               AND peerreview.week = 11 
        GROUP  BY student1.groupid, 
                  student2.studentID) a 
       INNER JOIN (SELECT groupid, 
                          Count(*) - 1 AS cnt 
                   FROM   student 
                   GROUP  BY groupid) b 
               ON a.groupid = b.groupid 
                  AND a.cnt = b.cnt

See SqlFiddle

edited May 28, 2012 at 10:40

answered May 28, 2012 at 9:55

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dgamma3 Over a year ago

great, can you explain what the student2 is?

Romil Kumar Jain Over a year ago

This is called aliasing. You can see that we are using student table two times in the query, so distinguish them we are giving them a nickname. Did the query worked for you.

dgamma3 Over a year ago

kind of. I think your subquery is giving me students who have been peer reviewed. I require students who have done the reviewing for all the peers in the group.

Romil Kumar Jain Over a year ago

@dgamma3 Now check the modified post.

Romil Kumar Jain Over a year ago

This query was solved with some tricks, so not able to convert it into easy steps.

Thomas · Accepted Answer · 2012-05-28 10:18:24Z

Select S.StudentId As Reviewer
    , S1.StudentId As StudentYetToBeReviewed
    , Weeks.WeekNum
From Student As S
    Join Student As S1
        On S1.GroupId = S.GroupId
            And S1.StudentId <> S.StudentId
    Cross Join  (
                Select 7 As WeekNum
                Union All Select 11
                ) As Weeks
Where Not Exists    (
                    Select 1
                    From PeerReview As P1
                    Where P1.reviewee = S1.StudentId
                        And P1.Week = Weeks.WeekNum
                    )
Order By WeekNum, reviewer

This provides you a list, by week, of the reviewer and the person they need to review. In the real solution, you would want to replace the Cross Join of weeks with a distinct list of weeks in which reviews should happen.

SQL Fiddle version

anouar.bagari · Accepted Answer · 2012-05-28 13:26:31Z

0

  select distinct s1.* 
  from student s1 inner join student s2 on s1.groupId = s2.groupeId
               left join peerreview pr on pr.revieweer = s1.studentId
                    and pr.reviewee =  s2.studentId
  where pr.Week = ? and  pr.revieweer is null and s1.studentId <> s2.studentId

edited May 28, 2012 at 13:26

answered May 28, 2012 at 10:54

anouar.bagari

2,10419 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL query - dynamic sub query

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related