Double select or join on same table ?

[gordy]

Is it possible to select two field value rows in the xf_user_field_value table?

The desired goal is to get username, vbench and real_name BUT the vbench and real_name are rows and not fields, how can a label or alias a row or field and then twice so I can gather the output, the value of vbench is a known, yet the real_name value is unknown.

My query:

SELECT m.*, username, field_value
FROM xf_user m LEFT JOIN xf_user_field_value p on p.user_id = m.user_id
WHERE field_value = "vb3nch"
ORDER BY username ASC;



mysql> select * FROM xf_user_field_value where user_id = 5240;
+---------+-----------+-------------------------------------------+
| user_id | field_id | field_value |
+---------+-----------+-------------------------------------------+
| 5240 | aim | |
| 5240 | country | united_kingdom |
| 5240 | facebook | |
| 5240 | gtalk | |
| 5240 | icq | |
| 5240 | msn | |
| 5240 | real_name | Carl |
| 5240 | skype | |
| 5240 | twitter | |
| 5240 | vbench | vb3nch |
| 5240 | wip_url | http://www.planetfigure.com/pages/vbench/ |
| 5240 | yahoo | |
+---------+-----------+-------------------------------------------+


Thank you for any help!
Cheers,
gordy

 

[Jake Bunce]

This will return all usernames and the values of those two fields.

SELECT u.username, ufv1.field_value, ufv2.field_value
FROM xf_user AS u
LEFT JOIN xf_user_field_value AS ufv1 ON (ufv1.user_id = u.user_id AND ufv1.field_id = 'vbench')
LEFT JOIN xf_user_field_value AS ufv2 ON (ufv2.user_id = u.user_id AND ufv2.field_id = 'real_name')
ORDER BY u.username
ASC;

 

[gordy]

Thank you sir! Makes complete sense now and really cleans up the page

 

[borbole]

You can also ommit the ASC part from the query as by default it is already sorted by that order.