mysql从两个表中查询两行

问题描述：

I have this Query to call information form two table.

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 ")

So if user is on friends list show username , i would like to get username and avatar. Avatar row is avatar . I try with this.

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 ")

And give me the error

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

where is the mistake?

我有这个查询从两个表调用信息。 p>

  DB :: get（“SELECT friends。*，（SELECT`login` FROM`users` WHERE`user``id` = friends .user_id`）AS`login` FROM`friends` WHERE`id_user` ='  “。$ this-＆gt; user ['id']。”'ORDER BY`id`DESC LIMIT“）
  code>  pre> 
 
 因此，如果用户在朋友列表中显示 用户名，我想获得用户名和头像。 头像行是头像 code>。  
我试试这个。 p> 
 
 
  DB :: get（“SELECT friends。*，（SELECT`login`，`* avatar *`FROM`users` WHERE`use`  .`id` = friends .user_id`）AS`login` FROM`friends` WHERE`id_user` ='“。$ this-＆gt; user ['id']。”'ORDER BY`id`DESC LIMIT“）  
  code>  pre> 
 
 并给我错误 p> 
 
 
  SQLSTATE [21000]：基数违规：1241操作数应包含1列 （s）
  code>  pre> 
 
 哪里出错？ p> 
  div>

答

First of all you should use Prepared Statement and Second, you can't write inline view, which has two columns

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

instead you should use JOIN, which might be efficient than current approach and more readable.

答

You need to use JOIN, e.g.:

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

mysql从两个表中查询两行

相关推荐