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".
Aucun commentaire:
Enregistrer un commentaire