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.

---

You could count the number of names in a subquery:

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

---

You probably have a bad design.

• The name table has a foreign key 'categories'.
• The 'categories'-table contains names of the categories.
• The name table references a category in the categories table.

Do both tables reference each other? Or is it just a result of the common names 'name' and 'category'?

If you have a 1:1 relationship, that means, every category has a name, and every name a category, you would put them in one table.

If you have an 1:n relationship, one category is bound to multiple names, the relation would be specified in the category table. If it is a n:1 relationship, the other way round.

I guess we need some sample data or more explanation, to be a real help.