ThePloki ThePloki - 5 months ago 9
SQL Question

SQL Query two tables and combine results (with a twist)

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?

Answer

THe useer whit the same parent user should be

SELECT users.*, users_info.* FROM users
INNER JOIN users_info on users_info.parent_id = 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')