From: | Gwork <nnj(at)riseup(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Postgresql query HAVING do not work |
Date: | 2017-01-05 01:23:43 |
Message-ID: | 7582ea1e-6146-fd8d-b564-c2fe251210b2@riseup.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-general |
Version: Postgresql 9.5
OS: Debian 8 jessie run on docker
Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+
Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results as
above instead of this:
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+
I don't know if I'm doing anything wrong?
Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.
From | Date | Subject | |
---|---|---|---|
Next Message | Vitaly Burovoy | 2017-01-05 01:52:43 | Re: Postgresql query HAVING do not work |
Previous Message | Michael Paquier | 2017-01-05 00:00:32 | Re: BUG #14485: Error in copy from temp |
From | Date | Subject | |
---|---|---|---|
Next Message | Vitaly Burovoy | 2017-01-05 01:52:43 | Re: Postgresql query HAVING do not work |
Previous Message | Tomas Vondra | 2017-01-05 00:18:57 | Re: Index impact on update? |