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.
精彩评论