The syntax you have listed is correct. I ran a test (in Microsoft SQL but there should not be a difference in this case) and the results were that two rows were returned. Are you only looking at the first row returned? Otherwise, there must be something wrong with your data because the query is right.
Moving forward, check to be sure you are looking at every row (not just the first). Next, check to be sure you have both an PK_ID=2 and PK_ID=3 in your table (maybe the 3 row is missing?). Finally, look at your database itself. There might be a corrupted index that is returning bad data (unlikely but possible).
Update:
Based upon your updated information, you need to change your query to use the IN statement instead of the equals statement and you need to take off the GROUP_CONCAT statement like so:
SELECT FIRST_NAME FROM USERDETAILS WHERE PK_ID IN (SELECT USER_ID FROM PROJECTS WHERE PROJECT_ID=2);
The one thing you need to watch out for is that the sub-query returns one column of data (the USER_ID column). That will be converted by SQL into something similar to your first query (in essence). If you return multiple rows, it will compare the value for each row against the PK_ID to see if it matches. If it does, it will include the row from the main query because of the match.
Submit an article or tip