help with sql query

[Guest]

everything looks right to me, but i'm not getting the results I want.  I want to have a list of all categories from table 1 and a count number based on table 2.  Right now I'm only getting returned the categories that are not null.

i.e. i want

category 1 - 50

category 2 - 50

category 3 - 0

category 4 - 50

etc.

right now I'm getting:

category 1 - 50

category 2 - 50

category 4 - 50

etc.

"SELECT table1.categoryName, ISNULL(COUNT(table2.id), 0) AS TotalCategories FROM table1 LEFT OUTER JOIN table2 ON table1.categoryName = table2.category WHERE (table2.expireDate >= GETDATE()) GROUP BY table1.categoryName ORDER BY TotalCategories DESC"

I'd settle for just that answer, but if you're feeling extra helpful today:

once the query is doing what i need...  i then would like to put this info into a string of dynamic text like so

50,50,0,50

(if you haven't guessed, i'm building a pie chart using google charts api).

Thank you in advance for an answer to my oversight.

[bregent]

You are not using a group by clause so count() is counting all rows.

[Guest]

can you show what you're suggesting?  i want it to count all rows, and there is a group by, so I'm not sure I understand your response.

i want to list all categories from table 1 (including those that don't exist in table 2), and provide the corresponding count from table 2 for all that do exist as well.

i.e.

i'm getting this right now with the above sql:

category 1 - 50

category 2 - 50

category 4 - 50

but what I want is this:

category 1 - 50

category 2 - 50

category 3 - 0   (this category doesn't exist in table 2)

category 4 - 50

[jon8 · Accepted solution]

SELECT t1.categoryName, ISNULL(COUNT(t2.id), 0) AS TotalCategories

FROM table1 t1

LEFT JOIN table2 t2 ON t1.categoryName = t2.category AND t2.expireDate >= GETDATE()

GROUP BY t1.categoryName

ORDER BY TotalCategories DESC

[bregent]

>i want it to count all rows, and there is a group by,

>so I'm not sure I understand your response.

Sorry, didn't see the group by clause. It's time for new glasses