MY SQL Query Join and search via corresponding ids

I have no experience with SQL but apparently it is the only way to achieve what I am after.

I have two tables one called pp_users and the other pp_userdata

pp_users contains information on each user of the site. Each user has an ID and an email address along with some additional information. The column structure is.

ID  -  user_email

pp_userdata contains additional information in the form of meta_keys with an id that corresponds to the ID in pp_users. So the structure shows a series of add开发者_开发技巧itional information for each user ID. The format is.

meta_id   -   user_id   -   meta_key   -   meta_value

The two meta keys that we are after are the value of first_name and last_name

So the intention is to get each user in pp_users and then order them by the the meta_key last_name using the corresponding user_id in pp_userdata.

After some reading I believe this required the two tables to JOIN but I have no idea how to get a meta_key by corresponding user_id.

Any ideas?

Marvellous

Your join syntax to get a result set like:

+------+------------------+----------+-----------+
| id   | user_email       | lastname | firstname |
+------+------------------+----------+-----------+
|    1 | test@example.com | Doe      | John      |
+------+------------------+----------+-----------+

Would be:

 SELECT    id, 
           user_email, 
           meta_lastname.meta_value lastname,  
           meta_firstname.meta_value firstname 
 FROM      pp_users 
 LEFT JOIN pp_userdata meta_lastname 
 ON        meta_lastname.user_id = pp_users.id 
 LEFT JOIN pp_userdata meta_firstname 
 ON        meta_firstname.user_id = pp_users.id 
 WHERE     meta_lastname.meta_key = 'last_name' 
 AND       meta_firstname.meta_key = 'first_name'

This query joins to the pp_userdata table multiple times, with different aliases (here the aliases are meta_firstname and meta_lastname). You can do this as many times as you like to get values from your metadata table - one join per key type. If you keep the alias names meaningful it's easy to keep track of which joined table is providing which columns in the result.

To get all the meta_users rows associated with a given user, you can use the following query.

select meta_users.* 
from pp_users
join meta_users
on pp_users.id = meta_users.user_id
where user_id = (some user ID here);

To get a specific key related to a given user, you can make it more specific.

select meta_users.* 
from pp_users
join meta_users
on pp_users.id = meta_users.user_id
where user_id = (some user ID here)
and meta_key = (some key name here);

To order by last name:

select meta_users.* 
from pp_users
join meta_users
on pp_users.id = meta_users.user_id
where user_id = (some user ID here)
and meta_key = 'last_name'
order by meta_value;

SELECT u.ID, u.user_id, a.meta_key, a.meta_value, b.meta_key, b.meta_value
FROM pp_users u
JOIN pp_userdata a ON u.ID = a.user_id AND a.meta_key='last_name'
JOIN pp_userdata b ON u.ID = b.user_id AND b.meta_key='first_name'
ORDER BY a.meta_value

Alternatively you can move the first_name and last_name part to the WHERE clause

SELECT u.ID, u.user_id, a.meta_key, a.meta_value, b.meta_key, b.meta_value
FROM pp_users u
JOIN pp_userdata a ON u.ID = a.user_id 
JOIN pp_userdata b ON u.ID = b.user_id
WHERE a.meta_key='last_name' AND b.meta_key='first_name'
ORDER BY a.meta_value