Using JOIN with where clause 1

[momer123]

I have a table "kcionly" and I want to retrieve count of unique records for a field "ncom_1" in that table. I have another table "categories" with a field name category that just has numbers from 1 to 20.

I use the below query to get the count corresponding to values, 1,2,3,....

I always want this list to be ordered from 1 to a specific integer

I am using this query

SELECT categories.CATEGORY, Count(kcionly.ncom_1)
FROM kcionly RIGHT JOIN categories ON kcionly.ncom_1 = categories.CATEGORY 
GROUP BY categories.CATEGORY;

This works well, but what if I want to include only those records depending on some other condition, example- kcionly.status=1


I have other ways to do this without joins but I have found that they are very slow methods. Without any where clause, they are almost 10 times slower than using JOINS to get the same output.

So I want to know if it is possible to edit the above query using joins and with the where condition.

Output:

1 2
2 10
3 45
4 17
5 0
6 10
7 0
8 0
9 0
10 0
etc.


Appreciate any help in this.

 

[r937]

when using an OUTER JOIN, any conditions on the deficient table belong in the ON clause, not the WHERE clause

SELECT categories.CATEGORY
     , Count(kcionly.ncom_1)
  FROM kcionly 
RIGHT 
  JOIN categories 
    ON kcionly.ncom_1 = categories.CATEGORY 
   [b]AND[/b] kcionly.status=1
GROUP    
    BY categories.CATEGORY

r937.com | rudy.ca

 

[momer123]

Thats great. Thanks r937