ThePloki ThePloki - 5 months ago 8
SQL Question

MySQL Query INNER JOIN with aliases

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

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

Comments