MySQL从表y中的类别中选择其中Count(来自另一个表的类别产品)大于零 [英] MySQL Select from category in table y where Count (of the category's product from another table) is greater than zero

问题描述

我正在使用下面的代码获取类别列表

I am fetching list of categories using the code below

`mysql_select_db($database_localhost, $localhost);
$query_category = "SELECT category, categoryID FROM categories";
$category = mysql_query($query_category, $localhost) or die(mysql_error());
$row_category = mysql_fetch_assoc($category);`

然后我使用 do while 代码列出类别:并在代码中计算该类别下的产品数量

Then i list the categories using the do while code: and within the code i count the number products under that category

<?php do { ?>
<li><?php  echo $row_category['category']; ?>" 


<span class="badge pull-right"><?php
$cadid = $row_category['categoryID'];
$resultcatd = mysql_query("SELECT  categoryID  FROM products WHERE   categoryID=$cadid "); 
$rowcatd = mysql_num_rows($resultcatd);  
echo $rowcatd;
?> </span>
</li>
<?php } while ($row_category = mysql_fetch_assoc($category)); ?> 

结果变成了

现在我不想显示零产品的类别:有没有人有更好的修改代码的方法$query_category = "SELECT category, categoryID FROM Categories";只选择有产品的类别

Now i dont want the categories that have zero products to be displayed: Is there someone with a better way of modifying the code $query_category = "SELECT category, categoryID FROM categories"; only to select categories that have products

推荐答案

你可以使用EXISTS:

SELECT category, categoryID 
FROM categories AS c
WHERE EXISTS (SELECT 1 
              FROM products AS p
              WHERE p.categoryID  = c.categoryID )

或INNER JOIN:

SELECT DISTINCT category, categoryID 
FROM categories AS c
INNER JOIN products AS p ON p.categoryID  = c.categoryID

或 IN 运算符:

SELECT category, categoryID 
FROM categories 
WHERE categoryID IN (SELECT categoryID FROM products)

这篇关于MySQL从表y中的类别中选择其中Count(来自另一个表的类别产品)大于零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！