One table requires multiple values ​​from different rows / tuples

Question

I have tables like:

'profile_values'
userID | fid     | value  
-------+---------+-------
1      | 3       | [e-mail]
1      | 45      | 203-234-2345
3      | 3       | [e-mail]
1      | 45      | 123-456-7890

and

'users'
userID | name       
-------+-------
1      | joe      
2      | jane     
3      | jake    

I want to join them and have one line with two values, for example:

'profile_values'
userID | name  | email          | phone
-------+-------+----------------+--------------
1      | joe   | [e-mail]  | 203-234-2345
2      | jane  | [e-mail] | 123-456-7890

I solved it, but it feels awkward, and I want to know if there is a better way to do this. The value of decisions that are more readable or faster (optimized) or simply best practices.

Current solution: several tables selected, many conditional statements:

SELECT u.userID AS memberid,
       u.name AS first_name, 
       pv1.value AS fname,
       pv2.value as lname
FROM  users AS u,
      profile_values AS pv1, 
      profile_values AS pv2,
WHERE u.userID = pv1.userID
  AND pv1.fid = 3
  AND u.userID = pv2.userID
  AND pv2.fid = 45;

Thanks for the help!

Answer 1

This is a typical summary query:

  SELECT u.userid,
         u.name,
         MAX(CASE WHEN pv.fid = 3 THEN pv.value ELSE NULL END) AS email,
         MAX(CASE WHEN pv.fid = 45 THEN pv.value ELSE NULL END) AS phone,
    FROM USERS u
    JOIN PROFILE_VALUES pv ON pv.userid = u.userid
GROUP BY u.userid, u.name

"LEFT" "JOIN", , PROFILE_VALUES.

Answer 2

, (@OMG Ponies), MySQL. , . , , SQL-, .

, -, .