Help counting rows from other table

[maltrecho]

SELECT one.this, COUNT(two.this) FROM one, two WHERE (two.this = one.this)The problem with this query is that it won't return rows from table "one" if they don't match with at least one element on table "two". How to proceed then with just one query?

[oracleguy]

I understand your problem, but I'm having a little trouble picturing what you are trying to do but I'll try and offer some helpful suggestions.

You could do a different type of join on the tables like a left join. I think that'd be the general direction in which you need to look at. If you are using MySQL 4.1 or higher you could do it very easily with a sub-query as well.

[maltrecho]

As simple as for example there are two tables, one with categories and another with pictures

table cats:

id name
-- ----
1 People
2 Animals
3 Things

table pics:

id cat name
-- --- ----
1 1 George
2 1 Mary
3 3 Table
4 1 Bryan
5 3 Car

"SELECT cats.name, COUNT(pics.name) AS total FROM cats, pics WHERE (cats.id = pics.id)"

returns:

People 3
Things 2

But never "Animals 0", how can I go with that using this example querying just once?