Vince Vince - 11 days ago 5
MySQL Question

How to do a SELECT on a "AS" value in MySQL.

I have this query below. It delivers the names of categories and their depth in the hierarchy. The depth is an "AS" value. However, I want to be able to select on only those names which have a specific depth. ie depth = 3. I have googled this to death and tried creating a virtual column, but I'm not that good at sql. Any advice ? TKS !

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY depth

Answer

You need to use a HAVING clause. This is a lot like the WHERE clause but it is computed later in the process and so can work on aggregated columns:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING depth = 3
ORDER BY depth

Depending on your database engine you might need to write it as:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING (COUNT(parent.name) - 1) = 3
ORDER BY depth