SQL query with join, count and

Question

I have 2 tables, and I'm trying to make one query to save myself some work.

Table 1: id, category id, colour Table 2: category id, category name 

I want to join them to get id, category id, category name, colour

Then I want to limit it so that no red elements 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 tried:

 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 that just doesn't work. I have tried many options and do not get any results when I try to execute the above query.

Answer 1

You must use GROUP BY so that you 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 

Answer 2

 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 

Answer 3

I used a sub-query and it works 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