MYSQL - SELECT from multiple rows, same user, different values

Question

Below is my table called fittest. I need to find which students based on student id (studid) have taken the pre and post test as designated in the prepost column. So based on the simple table below I would need to return studid 123456. How do I write the SELECT query for this?

SQL query: SELECT studid, prepost FROM `fittest` LIMIT 0, 30 ; 

    studid  prepost
    123456  pre
    123456  post
    1031460 pre

Answer 1

Try

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2

or if you want to ensure that only ids that have exactly one row with pre and one row with post then you can enforce it like this

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING (MAX(prepost = 'pre' ) + 
        MAX(prepost = 'post')) = 2
   AND COUNT(DISTINCT prepost) = 2

Output:

| STUDID |
----------
| 123456 |

Here is SQLFiddle demo for both queries

Answer 2

What about a JOIN:

select s1.studid, s1.prepost, s2.prepost
from fittest s1
inner join fittest s2
    on s1.studid = s2.studid
where s1.prepost = 'pre' 
  and s2.prepost = 'post'