CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mario
Mario

Reputation: 14741

SQL WHERE Temporary Column

I have this MSSQL Query which works

SELECT c.CategoryName + ' (' + cast(count(ic.CategoryId) as varchar(255)) + ')' AS
 CategoryName, count(ic.CategoryId) AS NumPhotos,
  c.Slug, c.ParentCategoryId, c.Id
FROM Categories
 c LEFT JOIN
 ItemCategories ic
 on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
ORDER BY ParentCategoryId DESC

And I want to return only rows, WHERE NumPhotos>0 but temporary columns are not allowed on SQL WHERE clause

Upvotes: 2

Views: 3266

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The having clause is the canonical solution to your problem. However, I suspect that an inner join would also be appropriate:

SELECT c.CategoryName + ' (' + cast(count(ic.CategoryId) as varchar(255)) + ')' AS CategoryName,
       count(ic.CategoryId) AS NumPhotos,
       c.Slug, c.ParentCategoryId, c.Id
FROM Categories c INNER JOIN
     ItemCategories ic
     on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
ORDER BY ParentCategoryId DESC;

Without sample data, it is hard to be sure, but I'm pretty sure this does the same thing.

The advantage of an inner join is that it might be more efficient, because less (maybe only slightly less) data would be processed and the optimizer has more opportunities to pick the best join algorithm.

Upvotes: 2

Turophile
Turophile

Reputation: 3405

Existing comments have given adequate answers, but here is another solution, using a "virtual table":

SELECT * FROM ( 
SELECT c.CategoryName + ' (' + cast(count(ic.CategoryId) as varchar(255)) + ')' AS
 CategoryName, count(ic.CategoryId) AS NumPhotos,
  c.Slug, c.ParentCategoryId, c.Id
FROM Categories
 c LEFT JOIN
 ItemCategories ic
 on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
) 
WHERE NumPhotos>0
ORDER BY ParentCategoryId DESC

Upvotes: 1

Related Questions