JOIN / QUERY issue

[Onisutra]

I'm having this issue displaying the results I need. The issue is that I cannot condense my results and end up with extra. The code I currently have is below.

SELECT category.*, content.*
FROM `category`
LEFT JOIN `content` ON category.categoryid = content.parentid
WHERE category.parentid = 1
ORDER BY category.title

Here are some example results I get from running this.

(categoryid, title, contentid, parentid, name, date)
1 | title1 | 1 | 1 | name1 | 0
1 | title1 | 2 | 1 | name2 | 1
2 | title2 | 3 | 2 | name3 | 0
2 | title2 | 4 | 2 | name4 | 1

What results I would like to see are

(categoryid, title, contentid, parentid, name, date, count)
1 | title1 | 2 | 1 | name2 | 1 | 2
2 | title2 | 4 | 2 | name4 | 1 | 2

I would like to get results that count the amount of content rows by the category they are related to. I would also like retrieve the oldest by its date.

I hope that was clear enough.
Thank you for your time.

[drjohn]

It looks as if your query is giving you exactly the correct results.

If you want a count(*) then you need to use a group by condition, and also, amazingly, include count(*) in the original query.

But in case you haven't noticed, the count for the two desired rows you have given is wrong - there is ONE title1 with name1, one title1 with name2, one title2 with name3 and one title3 with name4.

Do you really want, instead, a count of how many rows title1 is associated with in the second table, and how many rows title2 is associated with in the second table?

[Onisutra]

Thank you that was helpful, I didn't know I needed the group tag. Here is the query I used.

SELECT category.categoryid, category.name, content.contentid, content.name AS recent, content.date, COUNT(content.contentid) as count 
FROM category AS category
LEFT JOIN content AS content ON (category.categoryid = content.parentid)
WHERE category.parentid = 1
GROUP BY category.name