结合两个MYSQL SELECT查询 [英] Combine two MYSQL SELECT queries

问题描述

可能重复:
如何合并两个Post/Category表MYSQL SELECT查询合并为一个

Possible Duplicate:
How to combine two Post/Category tables MYSQL SELECT queries into one

我有两个MYSQL查询:

I have two MYSQL queries:

1) "SELECT ID,post_title,post_category,post_perma FROM ".TBL_POSTS."
   WHERE  published='1' AND page='0' ORDER BY  ID ASC LIMIT 50"

2) "SELECT p.cat_ID,p.cat_nicename FROM ".TBL_CATEGORIES." n, 
   ".TBL_CATEGORIES." p
   WHERE n.lft BETWEEN p.lft 
   AND p.rgt AND n.cat_ID='".post_category."' ORDER BY p.lft

我这样使用它:

$sql="SELECT ID, post_title, post_category,post_perma 
      FROM ".TBL_POSTS." 
      WHERE  published='1' 
      AND page='0' 
      ORDER BY ID ASC LIMIT 50";

$result=mysql_query($sql);

while($row=mysql_fetch_assoc($result)){

$sql2="SELECT p.cat_ID, p.cat_nicename 
       FROM ".TBL_CATEGORIES." n, ".TBL_CATEGORIES." p 
       WHERE n.lft BETWEEN p.lft 
       AND p.rgt 
       AND n.cat_ID='".$row['post_category']."' 
       ORDER BY p.lft";

    $result2=mysql_query($sql2);

    while($row2=mysql_fetch_assoc($result2)){
        $path.='/'.$row2['cat_nicename'];
    }

    $link.='<a href="'.$path.'">'.$row['post_title'].'</a><br>';
    $path='';

}
echo($link);
exit;

}

这是我在链接中获取路径的方式..现在我想要的是:

This is how I get path in the link.. now What i want is:

我想合并两个查询，这样就不会在while循环中运行第二个查询. 变得非常糟糕，因为如果我收到100条帖子，则第二次查询将运行100次以获取路径.

I want to combine both queries so, that I do not have run the second query in while loop .. It gets very bad because if I receive 100 posts.. second query will run 100 times to fetch the path.

推荐答案

您可以像这样JOIN两个表:

SELECT 
  p.ID, 
  p.post_title,  
  c.cat_nicename, 
  p.post_perma 
FROM       TBL_POSTS p
INNER JOIN TBL_CATEGORIES c 
        ON p.CategoryID = c.Cat_ID
WHERE      p.published = '1' 
  AND      p.page = '0' 
ORDER BY   p.ID ASC 
 LIMIT 50

这篇关于结合两个MYSQL SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！