If I may throw my own 2 cents into the hat;
sdeming is absolutely right, you do need to perform a LEFT OUTER JOIN in order to retrieve a resultset regardless if there are categories with no articles. Reason being is, in a standard INNER JOIN, you're retrieving only the resultset that match all criteria, i.e. only categories with articles. If you change the query so you're performing a LEFT JOIN, it will retrieve
all records from the table on the LEFT side of the equation (makes sense, right? ;-) ) and only those from the table on the right that match the criteria. So for example, breaking your SQL statement down further, let's look at it from the standpoint of LEFT JOIN.
Code:
SELECT Category.CatID,Category.CatName,count(ArtCat.ArticleID)
Ok so in the SELECT, the tables we want to use in the resultset are Category and ArtCat. Notice that Category is the table declaration on the
left. It's that simple. If we wanted to retrieve all articles regardless if they have a category assigned to them, we would want all records from the table on the
right, and it would become a RIGHT OUTER JOIN.
When you declare a LEFT or RIGHT OUTER JOIN, you skip the table declaration in the FROM (as you've already found out) and use it in the declaration for the LEFT JOIN itself. You then use the keyword ON to match the columns up. In a simple INNER JOIN, the ON would be something like using the WHERE clause. Furthermore, if we have two columns from different tables that have the same name, you can use the USING keyword instead, like
Code:
SELECT Category.CatID,
Category.CatName,
count(ArtCat.ArticleID)
FROM Category
LEFT OUTER JOIN ArtCat
USING (CatID)
GROUP BY Category.CatID
Taking it one step further and cleaning the resultset up a bit, I'll use an ALIAS on the tables and on the COUNT so we have a little nicer formatting in the end, e.g.
Code:
SELECT c.CatID,
c.CatName,
COUNT(ac.ArticleID) AS ArticleCount
FROM Category AS c
LEFT OUTER JOIN ArtCat AS ac
USING (CatID)
GROUP BY c.CatID
So now, rather than having a result like COUNT(ArtCat.ArticleID) you can simply refer to it as ArticleCount (or whatever word strikes you as making sense). Strictly speaking, the OUTER keyword isn't needed, you can use simply LEFT or RIGHT join, but I find in circles where SQL is spoken most often they do use the full syntax in statements. I suppose it leaves very little chance for ambiguity in the statement.
Anyway, hope that clears things up a bit.
Linear Mode
