1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Double select or join on same table ?

Discussion in 'XenForo Development Discussions' started by gordy, Mar 3, 2012.

  1. gordy

    gordy Well-Known Member

    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


     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Code:
    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;
    
     
    borbole, gordy and Rigel Kentaurus like this.
  3. gordy

    gordy Well-Known Member

    :notworthy:

    Thank you sir! Makes complete sense now and really cleans up the page :)
     
  4. borbole

    borbole Well-Known Member

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

Share This Page