Advice on building a nested multi-select View with MySQL 5.x?

I need to build a view out of a table structure that I don't have the option of changing, as it's in use by another application. Here's a snippet from the actual data...





TABLE users


user_id


username


password


email








TABLE profile_fields


field_id


field_name





TABLE profile_values


field_id


user_id


value








Now, let's say that profile_fields is populated with something like this...





field_id = 1


field_name = 'firstname'





field_id = 2


field_name = 'lastname'








And profile_values has





field_id = 1


user_id = 1


value = 'Bob'





field_id = 2


user_id = 1


value = 'Smith'








And users has...





user_id = 1


username = 'bsmith72'


password = 'yeah-right'


email = 'bob@example.com'








What I need to do is build a select statement that will return columns and rows like this...





user_id, username, password, email, firstname, lastname


1, 'bsmith72', 'yeah-right', 'bob@example.com', 'Bob', 'Smith'Advice on building a nested multi-select View with MySQL 5.x?
SELECT u.*, f1.value AS firstname, f2.value AS lastname


FROM users u


LEFT OUTER JOIN profile_values f1 ON (u.user_id = f1.user_id AND f1.field_id=1)


LEFT OUTER JOIN profile_values f2 ON (u.user_id = f2.user_id AND f2.field_id=2)








----


If you don't know field IDs beforehand, the query will become more hairy:





SELECT u.*, f1.value as firstname, f2.value as lastname


FROM users u


LEFT OUTER JOIN profile_values f1 ON (u.user_id = f1.user_id AND f1.field_id=(SELECT field_id FROM profile_fields WHERE field_name = 'firstname'))


LEFT OUTER JOIN profile_values f2 ON (u.user_id = f2.user_id AND f2.field_id=(SELECT field_id FROM profile_fields WHERE field_name = 'lastname'))

natural facial