0

I have a mysql table with the following columns:

id (autoincremented primary key)
user_id
test_id
test_score

How can I get all test data tied to the users that have participated in test_id = 5. I want all test data (not just test_id 5) for the users that have participated in that particular test.

Improve this question
2
  • You meant not just user_id 5 ? Your first and second statements seem contradictory to me. Can you explain a bit more ? Commented Aug 3, 2011 at 6:23
  • @Mahesh If a user has taken test 5, return all of that user's test results for all tests. That's how I understand it. Commented Aug 3, 2011 at 6:31

4 Answers 4

Reset to default
2

I believe this will get you what you are looking for.

SELECT * 
  FROM table 
  WHERE user_id IN 
   (SELECT DISTINCT user_id 
    FROM table
      WHERE test_id = 5)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

How is this different from - select * from table where test_id = 5 ;. Even simpler.
The nested select is not as efficient as the join operation because it executes the nested select once for every row in TABLENAME
@Mahesh - Your query doesn't return the results he requires.
@jkschneider I'm no MySql expert, but I think in this case, the subquery is only executed once. There aren't any dependencies between the two queries, and the WHERE in the subquery has a hard value. I haven't run this through an Explain, but I think this is just 2 simple queries.
@jkschneider I believe AndrewR is correct about it only being executed once since it is not row-dependent. My issue, rather, is that this (and currently the other answers) won't use indexes if available.
1

Sounds like you'd want to do a self-join. Something like this would utilize indexes (assuming you have an index on user_id and an index on test_id, which you should):

SELECT DISTINCT t1.*
FROM `tableName` t1
INNER JOIN `tableName` t2 ON t2.user_id = t1.user_id
WHERE t2.test_id = 5

I guess that raises another point. How is your table indexed? Just the numeric primary key? You generally want to have indexes on all columns that are used in JOINs or WHERE clauses. Thus, you'd want to have an index on user_id and an index on test_id.

Can a user take the same test multiple times? If not, then you'd want restrict that by having a unique multiple-column ("composite") index across user_id and test_id together. And then add a regular index just to test_id for the WHERE clause.

Improve this answer

2 Comments

Although this produced the same results as @AndrewR solution, yours was a slight bit faster to execute. Thanks!
@VinnyD Glad to help. I think as your numbers of users and test results grow substantially, the difference in speed will become increasingly significant. With my basic tests (about 1000 records), this was one or two orders of magnitude faster than the other proposed solutions, thanks to the use of indexing.
0

If I understand your question correctly, then the basic approach is to join the table on itself.

select allTestData.* from TEST_TABLE allTestData
left outer join (select user_id from TEST_TABLE where test_id = 5) testIdFive
on allTestData.user_id = testIdFive.user_id
Improve this answer

Comments

0

Try this Query,

SELECT test data 
FROM TABLENAME 
WHERE user_id IN (
  SELECT user_id 
  FROM TABLENAME 
  WHERE test_id = 5
)
Improve this answer

2 Comments

The nested select is not as efficient as the join operation because it executes the nested select once for every row in TABLENAME
here you can get all testdata's tied to the users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.