MySQL Query INNER JOIN with aliases

Question

I have two tables: users and users_info

users looks like this:

+----+----------+-------+
| id | slug     | name  |
+----+----------+-------+
|  1 | theploki | Kris  |
+----+----------+-------+

and users_info looks like this:

+----+--------+----------+---------------+
| id | parent | info_key | info_val      |
+----+--------+----------+---------------+
|  1 | 1      | email    | kris@kris.com |
+----+--------+----------+---------------+
|  2 | 1      | age      | 28            |
+----+--------+----------+---------------+

I want to SELECT a user who has user_info email = 'kris@kris.com'
- and -
return ALL user_info values and users values

Here's the result I'm looking for:

+----+----------+-------+---------------+-----+
| id | slug     | name  | email         | age |
+----+----------+-------+---------------+-----+
|  1 | theploki | Kris  | kris@kris.com | 28  |
+----+----------+-------+---------------+-----+

So far the closest I've gotten is with this query:

SELECT users.*, users_info.* FROM users
INNER JOIN users_info on users_info.parent = users.id
where users.id = (SELECT users_info.parent FROM users_info
    WHERE users_info.parent = users.id
    AND users_info.info_val = 'kris@kris.com')

And it returns this result:

+----+----------+-------+----+--------+----------+---------------+
| id | slug     | name  | id | parent | info_key | info_val      |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  1 |  1     | email    | kris@kris.com |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  2 |  1     | age      | 28            |
+----+----------+-------+----+--------+----------+---------------+

Obviously I don't need the id of the users_info result and I want each info_key to be the "alias" (or column name) and each info_val to be the value for that "alias".

Answer 1

For this case, you can do it like this;) Just a simple table pivot.

select
    users.id,
    users.slug,
    users.name,
    max(if(users_info.info_key = 'email', users_info.info_val, null)) as email,
    max(if(users_info.info_key = 'age', users_info.info_val, null)) as age
from users
inner join users_info
on users.id = users_info.parent
group by users.id

SQLFiddle DEMO HERE

If you have a dynamic info_key, you will need a dynamic sql to do this, here I give you a sample.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(if(users_info.info_key = ''',
      users_info.info_key,
      ''', users_info.info_val, null)) as ',
      users_info.info_key
    )
  ) INTO @sql
FROM users
inner join users_info
on users.id = users_info.parent
;

SET @sql = CONCAT('select users.id, users.slug, users.name, ', @sql, ' FROM users
inner join users_info group by users.id having email = \'kris@kris.com\'');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle DEMO HERE