PHP投票系统查票

Question

我正在开发一个 PHP 投票系统，我想检查登录用户对帖子的投票情况，并在用户投票后显示已投票。我想在一个查询中完成，而不使用多个查询。帖子表

+---------+--------+--------+
| post_id | title  | c_id   |   
+---------+--------+--------+
|       1 | post 1 |   2    |
|       2 | post 2 |   3    | 
|       3 | post 3 |   2    | 
|       4 | post 4 |   1    |
+---------+--------+--------+

MySQL 循环

SELECT * 
  FROM posts 
  LEFT 
  JOIN categories 
    ON categories.cat_id = posts.c_id 
 WHERE posts.c_id = categories.cat_id 
   AND posts. active = 1 
 ORDER 
    BY posts.post_id DESC 
 LIMIT 0, 10

投票表

+---------+--------+---------+
| vote_id | p_id   | u_id    |  
+---------+--------+---------+
|       1 |   1    |   1     |
|       2 |   2    |   1     | 
|       3 |   2    |   2     | 
|       4 |   4    |   1     |
+---------+--------+---------+

登录用户 = $uid因此，如果我在上面的 MySQL 循环中运行查询，它就可以正常工作

SELECT * FROM votes WHERE u_id = $uid AND p_id= $post_id

有没有办法结合这两个查询？

Answer 1

SELECT * 
  FROM posts P
  LEFT 
  JOIN categories C
    ON C.cat_id = P.c_id 
  LEFT JOIN (SELECT p_id FROM votes WHERE u_id = $uid)  V 
    ON V.p_id=P.post_id
   WHERE P. active = 1  
 ORDER 
    BY P.post_id DESC 
 LIMIT 0, 10 ;