SQL查询两个表并合并结果（略有不同）

debugcn 发表于 Dev

ThePloki

我有两个表：users和users_info

users 看起来像这样：

+----+----------+-------+
| id | slug     | name  |
+----+----------+-------+
|  1 | theploki | Kris  |
+----+----------+-------+

和users_info看起来像这样：

+----+--------+----------+---------------+
| id | parent | info_key | info_val      |
+----+--------+----------+---------------+
|  1 | 1      | email    | [email protected] |
+----+--------+----------+---------------+
|  2 | 1      | age      | 28            |
+----+--------+----------+---------------+

我想SELECT一个user谁拥有user_info电子邮件=“[email protected]”
-和-
返回所有user_info结果具有匹配users_info.parent = user.id
-和-
以可读的格式（如result['email'] = '[email protected]'和result['id'] = 1和result['name'] = 'Kris'和result['age'] = 28）

我确定这已在另一个问题中得到了解答，但我一直在搜索和阅读对我而言并不完全有效的答案。

显然，我确保info_keys不能匹配users列名。

我试过内联接，外联接，只是普通联接，而我试过根本没有任何联接。

这不起作用，但是有点解释了我想做什么：

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 = '[email protected]'
    GROUP BY users_info.parent)
GROUP BY users.id

有时我不会搜索信息列，有时我不会搜索用户列，这就是为什么我把 1=1

也许执行两个查询然后将它们组合起来会更简单？

更新：

OK，这是更新的SQL查询（由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 = '[email protected]')

这非常接近，但是返回如下结果集：

+----+----------+-------+----+--------+----------+---------------+
| id | slug     | name  | id | parent | info_key | info_val      |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  1 |  1     | email    | [email protected] |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  2 |  1     | age      | 28            |
+----+----------+-------+----+--------+----------+---------------+

但我想要这样的结果集：

+----+----------+-------+---------------+-----+
| id | slug     | name  | email         | age |
+----+----------+-------+---------------+-----+
|  1 | theploki | Kris  | [email protected] | 28  |
+----+----------+-------+---------------+-----+

scaisEdge

与同一父用户相同的用户应该是

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 = '[email protected]')

仅作为示例，您可以使用列名和别名更改user。* ord users_info。*

SELECT users.slug,  users.name as name, group_concat(users_info.info_val) 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 = '[email protected]')
 group by users.name

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。