Select some data while counting entries from a different table?

[wh33t]

Hey SP,

I've been trying to learn how to use Mysql more effectively in hopes that it will continue to reduce the amount of PHP I have to write to get data from the database. It's been quite rewarding so far and now I have a new challenge I'm trying to over come.

I have a table called "categories" and a table called "items". The categories are differentiated (or w/e word I'm supposed to be using) via unique, auto-incremented category_id of int11. The items are differentiated via item_id of int11 as well and the items table has a column called category_id which is a foreign_key? of categories.

So what I'd like to do is select all categories and count the amount of items in that category by doing something like "select * from categories and "item_count" as (select count(*) from items where category_id = xxxx)" in which case the result will yield an array in PHP that gives me access to "item_count" as $array['item_count'] as well as all of the columns of the table "categories".

I've tried reading some tutorials from the web and I'm confident I could get this working but that doesn't mean I'll understand what it is I'm doing and I find that to be rather pointless and I've had such good results with what I learn here on sitepoint.

Thanks for reading and any tips you share.

[cpradio]

You want to join the tables and group them.

SELECT category_id, name, COUNT(item_id) AS 'item_count' FROM categories AS c 
    LEFT JOIN items AS i ON c.category_id = i.category_id 
    GROUP BY category_id, name

[wh33t]

You want to join the tables and group them.

SELECT category_id, name, COUNT(item_id) AS 'item_count' FROM categories AS c 
    LEFT JOIN items AS i ON c.category_id = i.category_id 
    GROUP BY category_id, name

Wow, speedy reply. Would you mind explaining what GROUP BY is doing? I think I get the COUNT(item_id) as 'item_count'.

[cpradio]

A join will create a record for each combination, so let's say you have 2 categories and 3 items in each category, so you would get 6 results.
Example:

Category_ID                 Name                   Item_ID            Item Name                   Price
----------------------------------------------------------------------------------------------------------------------------------
1                           Category 1              1                 Item 1                      5.00
1                           Category 1              2                 Item 2                      6.00
1                           Category 1              3                 Item 3                      7.00
2                           Category 2              4                 Item 4                      8.00
2                           Category 2              5                 Item 5                      9.00
2                           Category 2              6                 Item 6                      10.00

The group by says, I want my results grouped by X, in this case category_id and name, so it will group the results by those columns
Example:

Category_ID                 Name                   
-----------------------------------------------------------------------------------------------------------------
1                           Category 1            
     ----------------------------------------------------------------------
     Item_ID            Item Name                   Price
     ----------------------------------------------------------------------
     1                  Item 1                      5.00
     2                  Item 2                      6.00
     3                  Item 3                      7.00

2                           Category 2            
     ----------------------------------------------------------------------
     Item_ID            Item Name                   Price
     ----------------------------------------------------------------------
     4                  Item 4                      8.00
     5                  Item 5                      9.00
     6                  Item 6                      10.00

With group by, you can only select the columns you group on. But you can add new columns so long as they are a result of COUNT(), SUM(), AVG(), MIN(), MAX(), etc.
I used COUNT(item_id), so that it would count the items in the category group, thus providing you with the category id, name and the number of items in that category.

Thus you end up with

Category_ID                 Name                   item_count
----------------------------------------------------------------------------------------------------------------------------------
1                           Category 1             3
2                           Category 2             3

[wh33t]

A join will create a record for each combination, so let's say you have 2 categories and 3 items in each category, so you would get 6 results.
Example:

Category_ID                 Name                   Item_ID            Item Name                   Price
----------------------------------------------------------------------------------------------------------------------------------
1                           Category 1              1                 Item 1                      5.00
1                           Category 1              2                 Item 2                      6.00
1                           Category 1              3                 Item 3                      7.00
2                           Category 2              4                 Item 4                      8.00
2                           Category 2              5                 Item 5                      9.00
2                           Category 2              6                 Item 6                      10.00

The group by says, I want my results grouped by X, in this case category_id and name, so it will group the results by those columns
Example:

Category_ID                 Name                   
-----------------------------------------------------------------------------------------------------------------
1                           Category 1            
     ----------------------------------------------------------------------
     Item_ID            Item Name                   Price
     ----------------------------------------------------------------------
     1                  Item 1                      5.00
     2                  Item 2                      6.00
     3                  Item 3                      7.00

2                           Category 2            
     ----------------------------------------------------------------------
     Item_ID            Item Name                   Price
     ----------------------------------------------------------------------
     4                  Item 4                      8.00
     5                  Item 5                      9.00
     6                  Item 6                      10.00

With group by, you can only select the columns you group on. But you can add new columns so long as they are a result of COUNT(), SUM(), AVG(), MIN(), MAX(), etc.
I used COUNT(item_id), so that it would count the items in the category group, thus providing you with the category id, name and the number of items in that category.

Thus you end up with

Category_ID                 Name                   item_count
----------------------------------------------------------------------------------------------------------------------------------
1                           Category 1             3
2                           Category 2             3

Thank you for the very elaborate explanation. I think I know what you mean by it all, I've just go to learn to think in those terms instead of the way I originally figured the query would be written. Would you say this is the only way to do such a query, or just the best?

[r937]

Would you say this is the only way to do such a query, or just the best?

just the best