CODEWITHSUNDEEP
mysqlsql
Basballguy
Basballguy

Reputation: 61

How do I get all rows meeting specific criteria?

I'm trying, in a single query, to get an entire set of rows when only one of those rows meets certain criteria. In the case below, I want to do a query for Mike Smith. If I find Mike Smith has taken a test (by test_id) then I want to include all the results for that test_id. So a successful query would return the first 7 rows. Is this possible without running multiple queries? Below is the example entire contents of my table.

I can't use

Select * where first_name = 'Mike';

as this will only return Mike's test scores;

I don't know how to select all test scores (for multiple tests) when I have a result for Mike. 

+------------+------------+-----------+-------+------+
| test_id    | first_name | last_name | class | rank |
+------------+------------+-----------+-------+------+
| 1          | John       | Doe       | 2012  | 1    |
+------------+------------+-----------+-------+------+
| 1          | Jack       | Smith     | 2014  | 50   |
+------------+------------+-----------+-------+------+
| 1          | Mike       | Smith     | 2014  | 60   |
+------------+------------+-----------+-------+------+
| 2          | Mike       | Smith     | 2014  | 70   |
+------------+------------+-----------+-------+------+
| 2          | John       | Smith     | 2014  | 80   |
+------------+------------+-----------+-------+------+
| 3          | Jake       | Smith     | 2014  | 80   |
+------------+------------+-----------+-------+------+
| 3          | Mike       | Smith     | 2014  | 90   |
+------------+------------+-----------+-------+------+
| 4          | Jake       | Smith     | 2014  | 78   |
+------------+------------+-----------+-------+------+

Upvotes: 0

Views: 86

Answers (2)

Solaris
Solaris

Reputation: 93

I think this might be what you're after:

SELECT * 
FROM test_scores
WHERE test_id IN
(
    SELECT test_id
    FROM test_scores
    WHERE first_name = 'Mike' AND last_name = 'Smith'
    GROUP BY test_id
)

Note: I just assumed the table name was 'test_scores'

Upvotes: 0

Phil
Phil

Reputation: 164729

Use the EXISTS clause, eg

SELECT * FROM `test_table` a WHERE EXISTS (
    SELECT 1 FROM `test_table` b
    WHERE first_name = 'Mike'
    AND last_name = 'Smith'
    AND b.test_id = a.test_id
)

Alternatively, you can INNER JOIN the table to itself, eg

SELECT a.* FROM `test_table` a
INNER JOIN `test_table` b
    ON a.test_id = b.test_id
WHERE b.first_name = 'Mike' AND b.last_name = 'Smith'

Demo here - http://sqlfiddle.com/#!2/c8646/1

Upvotes: 1

Related Questions