MySQL从两个表中查询两行

Question

我有此查询要调用信息表格两个表。

DB::get("SELECT friends. * , (SELECT `login` FROM `users` WHERE `users`.`id` = friends.`user_id`) AS `login` FROM `friends` WHERE `id_user`='" . $this->user['id'] . "' ORDER BY `id` DESC LIMIT ")

因此，如果用户在朋友列表上显示用户名，我想获得用户名和阿凡达。阿凡达行是 avatar。我尝试这个。

DB::get("SELECT friends. * , (SELECT `login`, `*avatar*` FROM `users` WHERE `users`.`id` = friends.`user_id`) AS `login` FROM `friends` WHERE `id_user`='" . $this->user['id'] . "' ORDER BY `id` DESC LIMIT ")

给我错误

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

错误在哪里？

Answer 1

首先，您应该使用准备好的语句，其次，您无法编写Inline视图，该视图具有两个列

SELECT friends. * , (SELECT `login`, `*avatar*` FROM ..

相反，您应该使用JOIN，它可能比当前方法高效，并且更可读。

Answer 2

您需要使用 JOIN，例如：

SELECT f.*, u.*
FROM friends f JOIN users u ON f.user_id = u.id
WHERE f.id_user = <your_id>
ORDER BY id DESC LIMIT <your_limit>;