Mysql return all results matching one column based on a value appearing at least once in another col-CodePudding

I'll try to explain what I need done...I have a table called 'quizzes' with data similar to this:

quiz_id	username	score
5	john	50
5	mary	75
9	mary	20
12	tom	15
12	john	13
12	anne	10
3	john	100
14	john	29

Note - there is only one person per quiz, it's like a record of 'high scores', so no person will have more than one row with a specific quiz_id.

What I want to do is pull all the results where John and at least one other person took that quiz, i.e. 'john' appears as a username and at one other person does too. So, the rows I would want returned are:

quiz_id	username	score
5	john	50
5	mary	75
12	tom	15
12	john	13
12	anne	10

As those are the only quizzes that john and at least one other person took, john didn't take 9, and only john took 3 and 14, so those aren't included either.

I've tried just SELECT quiz_id WHERE username = 'john' but obviously that only returns rows corresponding to his results.

CodePudding user response：

Using a join aggregation approach we can try:

SELECT t1.quiz_id, t1.username, t1.score
FROM yourTable t1
INNER JOIN
(
    SELECT quiz_id
    FROM yourTable
    GROUP BY quiz_id
    HAVING SUM(username = 'john') > 0 AND   -- john is present
           SUM(username <> 'john') > 0      -- someone other than john is present
) t2
    ON t2.quiz_id = t1.quiz_id
ORDER BY
    t1.quiz_id;