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.