How to join two tables together in SQL?

I have two tables. The name table have a foreign key named 'categories'. The other table is 'categories' that contains name of the categories. The name table references a category in the categories table.

So I use this:

$categories = "SELECT name, COUNT(*) category";
$number_of_categories = "SELECT category, COUNT(1) FROM name GROUP BY category";

As you can see, I'm doing two select statements. I heard that joining tables can do magic, please teach me how to do this in one SELECT statement.

Basically I want to SELECT all the rows from the category table, and then count the number of instances of each category in the name table.

---

SELECT c.name, COUNT(n.category) AS count_in_name_table
FROM categories c
LEFT JOIN name n ON n.category = c.name
GROUP BY c.name

---

Assuming that the name of the category table is "Category" (with category_name and category_id) and the name of the name table is "Name" (with name_id and category_id):

SELECT C.category_name, COUNT(N.name_id) FROM Category C 
LEFT JOIN Name N ON C.category_id=N.category_id
GROUP BY C.category_name

The grouping allows you to count all name entries per category name.

---

您可以计算子查询中的名称数量：

select  category.name
,       (
        select  count(*)
        from    category
        ) as CategoryCount
,       (
        select  count(*)
        from    name
        where   name.category = category.name
        ) as NameCount
from    category

链接地址: http://www.djcxy.com/p/63798.html

上一篇: 如何从一个MySQL数据库中为一个项目提取两个类别和标签？

下一篇: 如何在SQL中连接两个表？