help with sql statement - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21) +--- Thread: help with sql statement (/showthread.php?tid=15490) |
help with sql statement - El Forum - 02-06-2009 [eluser]adisaco[/eluser] If I have a table full of items, and each item has a category, what would be the sql statement that I can use to return html like this: category one (5 items) category two (34 items) category three (17 items) category four (2 items) category five (11 items) help with sql statement - El Forum - 02-06-2009 [eluser]davidbehler[/eluser] As I don't know what your tables look like in detail I would guess something like this should do the trick: Code: SELECT category_name, category_id, COUNT(*) AS item_count FROM category left join item on category.category_id = item.item_category_id GROUP BY category_name, category_id ORDER BY category_name ASC; help with sql statement - El Forum - 02-06-2009 [eluser]adisaco[/eluser] thx! it worked perfectly |