MySQL Vertical Join Multiple Tables

Question

I've got the following tables: users, attributes, attribute_values, user_attribute_values.

Don't ask why I chose to structure the tables like this, I'm just trying to learn SQL (MySQL to be more specific) by playing with different ideas.

Table structure is as follows:

users
+--+
|id|
+--+
| 1|
+--+

attributes
+--+---------+
|id|attribute|
+--+---------+
| 1|name     |
+--+---------+
| 2|age      |
+--+---------+

attribute_values
+--+---------------+
|id|attribute_value|
+--+---------------+
| 1|John Doe       |
+--+---------------+
| 2|30             |
+--+---------------+

user_attribute_values
+-------+------------+------------------+
|user_id|attribute_id|attribute_value_id|
+-------+------------+------------------+
|      1|           1|                 1|
+-------+------------+------------------+
|      1|           2|                 2|
+-------+------------+------------------+

I am trying to figure out a query that will result in something like this:

+-------+---------+---------------+
|user_id|attribute|attribute_value|
+-------+---------+---------------+
|      1|name     |John Doe       |
+-------+---------+---------------+
|      1|age      |30             |
+-------+---------+---------------+

Or better yet, something like this:

+--+--------+---+
|id|    name|age|
+-----------+---+
| 1|John Doe|30 |
+-------+-------+

Bear in mind that the proper keys and foreign keys have been added to all tables.

The closest I could get was something like this:

SELECT `users`.`id`, `attributes`.`attribute`, `attribute_values`.`attribute_value`
FROM `users`, `attributes`, `attribute_values`, `user_attribute_values`
WHERE `users`.`id` = `user_attribute_values`.`user_id` AND `user_attribute_values`.`attribute_id` = 1 AND `user_attribute_values`.`attribute_value_id` = 1;

Which results in:

+--+---------+---------------+
|id|attribute|attribute_value|
+--+---------+---------------+
| 1|     name|John Doe       |
+--+---------+---------------+
| 1|      age|John Doe       |
+--+---------+---------------+
| 1|     name|30             |
+--+---------+---------------+
| 1|      age|27             |
+--+---------+---------------+

Answer 1

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Proper joins should probably fix your problem:

SELECT u.id, a.attribute, av.attribute_value
FROM users u JOIN
     user_attribute_values uav
     ON u.id = uav.user_id JOIN
     attributes a
     ON uav.attribute_id = a.id JOIN
     attribute_values av 
     ON uav.attribute_value_id = av.attribute_value_id
WHERE av.id = 1 AND a.id = 1;

I'm not sure what the WHERE clause is supposed to be doing. I assume you really want WHERE u.id = 1 based on the sample results.