Wednesday, 27 May 2015

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

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?


SELECT studid, prepost FROM `fittest` LIMIT 0, 30 ; 

    studid  prepost
    123456  pre
    123456  post
    1031460 pre
 
Solution:
 
CREATE TABLE fittest (`studid` int, `prepost` varchar(4));
 
INSERT INTO fittest(`studid`, `prepost`) 
VALUES (123456, 'pre'),(123456, 'post'),(1031460, 'pre');  


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

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

Output
studid
123456

0 comments:

Post a Comment