One table, need multiple values from different rows/tuples
- by WmasterJ
I have tables like:
'profile_values'
userID | fid | value
-------+---------+-------
1 | 3 | joe@gmail.com
1 | 45 | 203-234-2345
3 | 3 | jane@gmail.com
1 | 45 | 123-456-7890
And:
'users'
userID | name
-------+-------
1 | joe
2 | jane
3 | jake
I want to join them and have one row with two of the values like:
'profile_values'
userID | name | email | phone
-------+-------+----------------+--------------
1 | joe | joe@gmail.com | 203-234-2345
2 | jane | jane@gmail.com | 123-456-7890
I have solved it but it feels clumsy and I want to know if there is a better way to do it. Meaning solutions that are either more readable or faster(optimized) or simply best-practice.
Current solution: multiple 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!
