MySQL query. Combine data based on two factors, then adjust how data is sorted based on values

Question

firstly, I'm new to polling multiple tables, so I'm sorry if this is a bit of a dumb question, but we all have to start somewhere!

I took a picture that should make it easier to understand:

http://www.mediumsliced.co.uk/temp/mysqlhelp.jpg

My first wp_user table has several columns - I need values ​​from 3 columns, which are user_email and user_nicename IDs.
My second wp_usermeta strong> table has 3 columns that store metadata for users. These columns are user_id, meta_key and meta_value. The user_id values ​​in this table always match the values ​​of the match identifier in wp_user (see Figure).

I would like to join the data from the meta_key fields along with 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 information I need, but the problem is that I really want to display the data from meta_key as separate columns and the meta value for this meta key in the correct row for this user based on their identifier. I also need to exclude all users who do not have wp_user_level as 0. (Again, hopefully this is clearer in my image that I provided)

Obviously, I need to learn a lot when it comes to MySql, but if someone could lead me to the final result, I would be very grateful, especially if you could explain the request so that I could learn from it, and not on just copy and paste it into place.

Thank you very much, if you need more information or if I need to clarify something, then feel free to ask!

Craig

Answer 1

 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 pointed out, one of the reasons this query is so cumbersome is because you need to use the EAV framework. The concept of the meta table is indeed anathema of relational design. Secondly, in order to get information from EAV, you need to create a so-called cross-column in which you will create the necessary columns in the query. In general, relational databases are not designed to generate columns on the fly, such as I do in my solution and are required for EAV.

Answer 2

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 provide you with what you need. Notes:

• format your SQL
• instead of the WHERE clause on id = user_id, it could be a connection

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: Regarding metadata and meta properties, you have to join meta_table as many times as there will be properties

to, for example, 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, EAV , although it allows you to reuse user interface elements, does not do wonders for the real database structure (slower and more complex queries, limited integrity checking and at the end of the code that works with such structures is more complex).

If possible, the right approach is to deal with “aggregation” at the application level.

Answer 3

Assuming all keys exist in wp_usermeta , you can just join the table several times. Use Left Join if values ​​may 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 4

You will need to join the meta table separately for each value that you want to pull out of it. This is one of the reasons why EAV tables are anti-pattern for relational databases.

Also, please learn to make explicit associations. Implicit joins will make it harder for you to work, as they can lead to random cross joins when things get complicated (and you will only write complex queries with the design of this poor one.) They are also bad when you need left joins (how you do it do in this poor design, since you don’t know if a person has the power for each attriubute.