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
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
pre and post values in real life. But I've added a query that eliminates possibilities of having rows with values other than pre and post.
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'
Depending on your real Primary Key, you use the following:
SELECT studid
FROM fittest
WHERE prepost = 'pre' OR prepost = 'post'
GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2
SELECT t1.studid
FROM fittest t1
INNER JOIN t2 ON t1.studid=t2.studid
WHERE t1.prepost = 'pre'
AND t2.prepost='post'
If Only PRE and POST are possible in this column then try
SELECT studid FROM fittest group by studid
HAVING Min(prepost)<>max(prepost)
SELECT
studid
FROM `fittest`
where prepost = 'pre'
or prepost = 'post'
group by studid
having count(distinct prepost)=2
SQL Fiddle with Extra records.
where prepost = 'pre' and prepost = 'post' will fail every time as that is not possible...
try:
select f1.studid
from fittest f1
inner join fittest f2
on f1.studid = f2.studid
where f1.prepost = 'pre' and f2.prepost = 'post'
here's the SQL Fiddle
You could use JOIN
SELECT a.studid
FROM fittest a
JOIN fittest b
ON a.studid=b.studid
and a.prepost like 'pre'
and b.prepost like 'post'
Select .... where studid in (123456,123457,...)
fittest where studid in (123456,123457,...)