MySQL query - Join data based on two factors, then customise the way the data is sorted based on values.

Question

Hello, firstly I'm new to querying multiple tables so I apologise if this is a bit of a stupid question, but we all have to start somewhere!

I've made a picture which should make this easier to understand:

My first table wp_user has several columns - I want the values from 3 columns which are ID user_email and user_nicename.
My second table wp_usermeta has 3 columns which stores meta data for the users. These columns are user_id, meta_key and meta_value. The user_id values in this table always correspond to the matching ID values in wp_user (see picture).

I would like to join data from the meta_key fields along with it's meta_value. So far I have this:

SELECT wp_users.ID, wp_users.user_login, wp_users.user_nicename, wp_users.user_email,
       wp_usermeta.user_id, wp_usermeta.meta_key, wp_usermeta.meta_value
FROM wp_users, wp_usermeta
WHERE (wp_users.ID = wp_usermeta.user_id);

This displays all the info I need however the issue I have is that I actually want to display the data from meta_key as individual columns and the meta_value for that meta key in the correct row for that user based on their ID. I also need to exclude any users which do not have their wp_user_level as 0. (Again hopefully this is clearer on my picture I provided)

Obviously I have a lot to learn when it comes to MySql but if anyone could guide me to the end result I'd be really grateful, even more so if you could explain the query so that I can learn from it as opposed to just copy and paste it in place.

Thanks a lot if you need more information or need me to clarify anything then feel free to ask!

Craig

Answer 1

Continuing your example

SELECT 
    wp_users.ID, 
    wp_users.user_login, 
    wp_users.user_nicename, 
    wp_users.user_email, 
    wp_usermeta.user_id, 
    wp_usermeta.meta_key, 
    wp_usermeta.meta_value 
FROM 
    wp_users, wp_usermeta 
WHERE 
   (wp_users.ID = wp_usermeta.user_id) AND (wp_usermeta.wp_user_level = 0);

should give you what you need. Notes:

• format your SQL
• instead of a WHERE condition on id=user_id, it could have been a join

like this:

SELECT 
    ...
FROM 
    wp_users INNER JOIN
    wp_usermeta ON wp_users.id = wp_usermeta.user_id
WHERE
    wp_usermeta.wp_user_level = 0

EDIT: As for the meta data and meta properties you have to join the meta_table as many times as there will be properties

so for example to get

SELECT 
    wp_users.ID, 
    wp_users.user_login, 
    wp_users.user_nicename, 
    wp_users.user_email, 
    m1.meta_value as first_name
    m2.meta_value as last_name
    m3.meta_value as address
    m4.meta_value as dob
FROM 
    wp_users 
    INNER JOIN wp_usermeta m ON wp_users.id = m.user_id AND m.meta_key = "wp_user_level" AND m.meta_value = 0
    LEFT JOIN wp_usermeta m1 ON wp_users.id = m1.user_id AND m1.meta_key = "first_name"
    LEFT JOIN wp_usermeta m2 ON wp_users.id = m2.user_id AND m1.meta_key = "last_name"
    LEFT JOIN wp_usermeta m3 ON wp_users.id = m3.user_id AND m1.meta_key = "address"
    LEFT JOIN wp_usermeta m4 ON wp_users.id = m4.user_id AND m1.meta_key = "dob"       

As you can see the EAV approach, although it allows great reuse of UI elements, does not do wonders for the actual structure of the database (slower and more complex queries, limited integrity validation and at the end code that operates on such structures is more complex).

If possible maybe the right approach is to deal with 'aggregation' on the application level.

Answer 2

Assuming that all keys exist in wp_usermeta, you can simply join the table several times. Use Left Join in case values might be missing.

Select
  u.ID,
  u.user_login,
  u.user_nicename,
  u.user_email,
  m_first_name.meta_value As first_name,
  m_last_name.meta_value  As last_name,
  m_address.meta_value    As address,
  m_dob.meta_value        As dob
From wp_users u
Join wp_usermeta m_first_name On (     m_first_name.user_id = u.id
                                   And m_first_name.meta_key  = 'first_name' )
Join wp_usermeta m_last_name  On (     m_last_name.user_id    = u.id
                                   And m_last_name.meta_key   = 'last_name' )
Join wp_usermeta m_address    On (     m_address.user_id      = u.id
                                   And m_address.meta_key     = 'address' )
Join wp_usermeta m_dob        On (     m_dob.user_id          = u.id
                                   And m_dob.meta_key         = 'dob' )
Join wp_usermeta m_user_level On (     m_user_level.user_id   = u.id
                                   And m_user_level .meta_key = 'm_user_level' )
Where m_user_level.meta_value = '0';

Answer 3

YOu will have to left join to the meta table separately for each value you want to pull out of it. This is one fo the reasons why EAV tables are an anti-pattern for relational databases.

Also please learn to do explicit joins. Implicit joins will get you in trouble as they can result in accidental cross joins when things get complex (and you will only be writing complex queries with a design this poor.) Also they are bad when you need to to left joins (as you will need to do inthis bad design as you don;t know if the person has a vlue for each attriubute.

Answer 4

Select wp_users.ID
    , wp_users.user_login
    , wp_users.user_email
    , wp_users.user_nicename
    , Min( Case When wp_usermeta.meta_key = 'first_name' Then wp_usermeta.meta_value End ) As first_name
    , Min( Case When wp_usermeta.meta_key = 'last_name' Then wp_usermeta.meta_value End ) As last_name
    , Min( Case When wp_usermeta.meta_key = 'address' Then wp_usermeta.meta_value End ) As address
    , Min( Case When wp_usermeta.meta_key = 'dob' Then wp_usermeta.meta_value End ) As dob
From wp_user
    Join wp_usermeta
        On wp_usermeta.user_id = wp_user.ID
Where Exists    (
                Select 1
                From wp_usermeta As Meta1
                Where Meta1.user_id = wp_user.id
                    And Meta1.meta_key = 'wp_user_level' 
                    And Meta1.meta_value = '0' 
                )
        And wp_usermeta.meta_key In('first_name','last_name','address','dob')                   
Group By wp_users.ID
    , wp_users.user_login
    , wp_users.user_email
    , wp_users.user_nicename

First, as others have mentioned, one of the reasons that this query is so cumbersome to write is that you are having to use an EAV structure. The concept of a "meta" table is really an anathema to relational design. Second, in order to get information out of an EAV, you have to create what is called a crosstab query where you build the columns you want in your query. In general, relational databases are not designed for on-the-fly column generation such as I'm doing in my solution and is required for EAVs.