CODEWITHSUNDEEP
phpmysql
Stephan Hovnanian
Stephan Hovnanian

Reputation: 189

how to get a count of records from Table B to place next to the name of each row in Table A

I have so many problems trying to understand this, so forgive me for what's most likely a repeat question.

I have 2 tables, one called "category" and one called "items". Each row in "items" has a category_id field that matches the category_id field in the "category" table.

In one mysql statement, I want to get a row count for each category_id in "items", but also retrieve the category_name in "category", so I can print out a list of categories like this:

The questions I've found here so far only seem to return the category_id's from "items", so the result would look like this:

Follow? I'm sure it can be done in one mysql statement, so far I've had to resort to two separate queries for the sole purpose of getting that little number.

Upvotes: 1

Views: 59

Answers (1)

Toby
Toby

Reputation: 8792

This should work for you;

SELECT c.name AS theName, COUNT(i.id) AS theCount
FROM category AS c JOIN items AS i ON i.category_id = c.id
GROUP BY i.category_id

//Edit

Sorry I should then explain this.

Select Is grabbing the category name and the count of the items.

From Is doing a join that will bind items and categories together.

Group By Is ensuring that the Category ID is the unique thing in the query.

Upvotes: 1

Related Questions