Mysql select query where column has two values [duplicate]

Question

I have a table in MySQL. In this I have columna called device and test_Id.

+-------+---------+
|test_id|   device|
+-------+---------+
| test_1|   Win PC|
| test_1|     Xbox|
| test_2|  Windows|
| test_3|   Win PC|
+-------+---------+

I want to select test_Id where device is both Win PC and Xbox. I have done like below

select test_id from table where device in ('Win PC', 'Xbox');

I am getting below result.

+----------+
| test_id  |
+----------+
| test_1   |
| test_3   |
| test_1   |
+----------+

But I want the result to be like

+----------+
| test_id  |
+----------+
| test_1   |
| test_1   |
+----------+

How can I do that?

Answer 1

The simplest query to achieve the result, a distinct list of test_id:

SELECT t.test_id 
  FROM mytable t 
 WHERE t.device IN ('Win PC','Xbox') 
 GROUP BY t.test_id
 HAVING COUNT(DISTINCT t.device) = 2

If we want to return individual rows, we could use that as an inline view, and do a JOIN to the outer table. Something like this:

SELECT d.test_id 
  FROM ( SELECT t.test_id 
           FROM mytable t 
          WHERE t.device IN ('Win PC','Xbox') 
          GROUP BY t.test_id
          HAVING COUNT(DISTINCT t.device) = 2
       ) q
  JOIN mytable d 
    ON d.test_id = q.test_id
 WHERE d.device IN ('Win PC','Xbox')

Another alternative would be use EXISTS correlated subqueries

 SELECT d.test_id
   FROM mytable d
  WHERE d.device IN ('Win PC','Xbox')
    AND EXISTS ( SELECT 1
                   FROM mytable t1
                  WHERE t1.test_id = d.test_id
                    AND t1.device = 'Win PC'
               )
    AND EXISTS ( SELECT 1
                   FROM mytable t2
                  WHERE t2.test_id = d.test_id
                    AND t2.device = 'Xbox'
               )

There are other query patterns that will achieve equivalent results.

Answer 2

select test_id 
from table 
where device = 'Win PC'
AND test_id IN (SELECT test_id 
from table 
where device = 'Xbox')

Answer 3

If you just want to know whether a test_id has both an Xbox and PC, you can do the following:

select test_id from 
   (select test_id, count(test_id) as cnt 
    from table 
    where device in ('Win PC', 'Xbox'), group by test_id) 
where cnt > 1