SQL Query with Join Count and Where

Question

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

User · Accepted Answer

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  lt  gt   red  GROUP   BY b category id  b category name

User · Answer

SELECT COUNT     table1 category id  table2 category name  FROM table1  INNER JOIN table2 ON table1 category id table2 category id  WHERE table1 colour  lt  gt   red  GROUP BY table1 category id  table2 category name

User · Answer

I have used sub-query and it worked great   SELECT    SELECT count    FROM  this- gt tbl news WHERE  this- gt tbl news cat id  this- gt tbl categories cat id  as total news FROM  this- gt tbl categories

[sql] SQL Query with Join, Count and Where

Examples related to sql

Examples related to join

Examples related to count

Examples related to where