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