How to find/list all the rows based on multiple values on a single column

I have a table which contains userID with their play status as True / False along with competitionID. One user can play multiple competitions. I have to find all the rows who played competitionID 5 & 6 both.

Table: contest_played

userID	play_status	competitionID
3	true	3
3	false	4
3	true	5
3	true	6
4	true	5
5	false	5
5	true	6
6	true	3
7	false	4
8	true	4
8	false	5
8	true	6
9	true	6
9	false	7

Based on above records, I need the result like below,

Result records

userID	play_status	competitionID
3	true	5
3	true	6
5	false	5
5	true	6
8	false	5
8	true	6

The userID who have not played both competitionID 5 & 6 should not be in the result.

Highly appreciate your help.

CodePudding user response：

Sam, in the future you should include a fiddle with the table structure and sample data, something like this:

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=cecab1b197c59c4bad448dec6f8cd3fd

That will help you get answers to your question, faster and probably better ones. Try this:

SELECT * 
FROM contest_played 
WHERE userID IN (
  SELECT userID FROM contest_played
  WHERE competitionID IN (5,6)
  GROUP BY userID
  HAVING count(competitionID) = 2
)
AND competitionID in (5,6);

Let me know if this produced the results you wanted. I was not sure if you wanted to only return results for competitionIDs 5 & 6, or if you wanted to return all results for the users who had played both. If you want to return all, just remove this line:

AND competitionID in (5,6)

CodePudding user response：

    SELECT t1.userID,
           t1.play_status,
           t1.competitionID
      FROM test_tbl t1
 LEFT JOIN test_tbl t2 ON t1.userID = t2.userID
     WHERE t1.competitionID IN (5, 6)
       AND t2.competitionID IN (5, 6)
  GROUP BY t1.userID,
           t1.play_status,
           t1.competitionID
    HAVING COUNT(t1.competitionID) = 2
  ORDER BY t1.userID,
           t1.competitionID