I have 2 tables and am trying to do one query to save myself some work.
Table 1: id, category id, colour Table 2: category id, category name I want to join them so that I get id, category id, category name, colour
Then I want to limit it so that no "red" items are selected (WHERE colour != "red") Then I want to count the number of records in each category (COUNT(id) GROUP BY (category id).
I have been trying:
SELECT COUNT(table1.id), table1.category_id, table2.category_name FROM table1 INNER JOIN table2 ON table1.category_id=table2.category_id WHERE table1.colour != "red" But it just doesn't work. I've tried lots of variations and just get no results when I try the above query.
3 Answers
You have to use GROUP BY so you will have multiple records returned,
SELECT COUNT(*) TotalCount, b.category_id, b.category_name FROM table1 a INNER JOIN table2 b ON a.category_id = b.category_id WHERE a.colour <> 'red' GROUP BY b.category_id, b.category_name SELECT COUNT(*), table1.category_id, table2.category_name FROM table1 INNER JOIN table2 ON table1.category_id=table2.category_id WHERE table1.colour <> 'red' GROUP BY table1.category_id, table2.category_name I have used sub-query and it worked great!
SELECT *,(SELECT count(*) FROM $this->tbl_news WHERE $this->tbl_news.cat_id=$this->tbl_categories.cat_id) as total_news FROM $this->tbl_categories 
