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
results that have a matching users_info.parent = user.id
- and -
in a readable format (like result['email'] = 'kris@kris.com'
and result['id'] = 1
and result['name'] = 'Kris'
and result['age'] = 28
)
I'm sure this was answered in another question but I've been searching and reading answers that don't exactly work for me.
Obviously I make sure info_key
s cannot match users
column names.
I've tried INNER JOINs, OUTER JOINs, just plain JOINs and I've tried without any JOIN at all.
This doesn't work, but it kind of explains what I want to do:
SELECT * FROM users
WHERE 1=1
AND users.slug='theploki'
AND (SELECT * FROM users_info
WHERE users_info.parent = users.id
AND 1=1
AND users_info.info_key = 'email'
AND users_info.info_val = 'kris@kris.com'
GROUP BY users_info.parent)
GROUP BY users.id
Sometimes I won't be searching for an info column, and sometimes I won't be searching for a users column which is why I put 1=1
Maybe it's just simpler to do two queries and combine them afterwards?
UPDATE:
OK, here's the updated SQL query (provided by scaisEdge):
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')
This is very close, but returns a result set like this:
+----+----------+-------+----+--------+----------+---------------+
| 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 |
+----+----------+-------+----+--------+----------+---------------+
but I want a result set like this:
+----+----------+-------+---------------+-----+
| id | slug | name | email | age |
+----+----------+-------+---------------+-----+
| 1 | theploki | Kris | kris@kris.com | 28 |
+----+----------+-------+---------------+-----+
Aucun commentaire:
Enregistrer un commentaire