lundi 27 juin 2016

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_keys 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