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

Answer 2

This utilizes a change in the schema to support casting of data coming back. And it hinges on the use of a stored procedure.

The maximum value of group_concat is governed by your setting for the following variable (which is usually defaulted rather low, like 1K):

set session group_concat_max_len = 20000;

Embed that call at the top of your stored proc under BEGIN. The manual page is here. The value can be huge. For instance, at least 4GB.

Schema

drop table if exists users;
create table users
(
    id int auto_increment primary key,
    slug varchar(100) not null,
    name varchar(100) not null
    -- other indexes here like uniqueness, etc (btw none added)
);

drop table if exists users_info;
create table users_info
(
    id int auto_increment primary key,
    parent int not null,
    info_key varchar(100) not null,
    info_val varchar(100) not null,
    datatype varchar(100) not null, -- see http://stackoverflow.com/a/8537070/ (DATA TYPES)
    -- other indexes here (btw none added)
    -- FK below:
    foreign key `ui_2_users_9283` (parent) references users(id) -- I guess
);

Load Test data;

-- delete from users; -- note truncate disallowed on parent with an FK (so delete !)
insert users(slug,name) values 
('theploki','Kris'),
('Yoda','Yoda');
-- select * from users;

-- truncate table users_info;
insert users_info(parent,info_key,info_val,datatype) values 
(1,'email','kris@kris.com','char(100)'),
(1,'age','28','unsigned'),
(2,'birthdate','1996-02-14','date'),
(2,'email','yoda@starwars.com','char(100)'),
(2,'networth','102504.12','decimal(12,2)'),
(2,'age','910','unsigned');

Stored Procedure:

drop procedure if exists fetchInfoKeysByEmailAddr;
DELIMITER $$
create procedure fetchInfoKeysByEmailAddr(emailAddr varchar(100))
BEGIN
    set @parentid=-1;
    select parent into @parentid 
    from users_info 
    where info_key='email' and info_val=emailAddr;

    if @parentid>0 then
        -- http://stackoverflow.com/a/8537070/ (DATA TYPES)

        SELECT GROUP_CONCAT(concat('cast("',info_val,'" as ',datatype,') as ',info_key)
        ORDER BY info_key SEPARATOR ',')  into @tail
        FROM users_info
        where parent=@parentid
        GROUP BY parent;

        set @final:=concat("select id,slug,name,",@tail,' from users where id=',@parentid);

        PREPARE stmt1 FROM @final;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    end if;
END$$
DELIMITER ;

Test:

call fetchInfoKeysByEmailAddr('x');
-- user info does not exist, empty (todo: change accordingly)

call fetchInfoKeysByEmailAddr('kris@kris.com');
+----+----------+------+-----+---------------+
| id | slug     | name | age | email         |
+----+----------+------+-----+---------------+
|  1 | theploki | Kris |  28 | kris@kris.com |
+----+----------+------+-----+---------------+

call fetchInfoKeysByEmailAddr('yoda@starwars.com');
+----+------+------+-----+------------+-------------------+-----------+
| id | slug | name | age | birthdate  | email             | networth  |
+----+------+------+-----+------------+-------------------+-----------+
|  2 | Yoda | Yoda | 910 | 1996-02-14 | yoda@starwars.com | 102504.12 |
+----+------+------+-----+------------+-------------------+-----------+

Due to the cast call embedded in the select, the data is brought back in its native, anticipated data type. Which means you can work on it directly.