SQL Query with Join, Count and Where -

i have 2 tables , trying 1 query save myself work.

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

i want join them id, category id, category name, colour

then want limit no "red" items selected (where colour != "red") want count number of records in each category (count(id) group (category id).

i have been trying:

select count(table1.id), table1.category_id, table2.category_name  table1  inner join table2 on table1.category_id=table2.category_id  table1.colour != "red" 

but doesn't work. i've tried lots of variations , no results when try above query.

you have use group by have multiple records returned,

select  count(*) totalcount,          b.category_id,          b.category_name     table1         inner join table2 b             on a.category_id = b.category_id    a.colour <> 'red' group   b.category_id, b.category_name