LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 01-22-2010, 09:34 AM   #1
h/w
Senior Member
 
Registered: Mar 2003
Location: New York, NY
Distribution: Debian Testing
Posts: 1,286

Rep: Reputation: 46
sql query; group by / having


Hello,
I'm trying out the examples given here using nested sets for dealing with hierarchical data in databases: http://dev.mysql.com/tech-resources/...ical-data.html

My question's regarding the example in the section 'Find the Immediate Subordinates of a Node', which provides the following query:

Code:
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
		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
HAVING depth <= 1
ORDER BY node.lft;
I'm using sqlite3 for my testing, and on running the above query, my results are:
Code:
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| Flash                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+
instead of the one given in the example, viz.:
Code:
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+
Can someone please explain why the having expression does not work, and instead of only selecting the node.name's that have depth <= 1, selects them all (as seen from 'Flash 2')?

Thanks in advance.

Last edited by h/w; 01-22-2010 at 09:37 AM.
 
Old 01-22-2010, 04:39 PM   #2
h/w
Senior Member
 
Registered: Mar 2003
Location: New York, NY
Distribution: Debian Testing
Posts: 1,286

Original Poster
Rep: Reputation: 46
Nevermind, this was brought up elsewhere - http://stackoverflow.com/questions/8...ry-not-working

The working query is:
Code:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS dpth
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
		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
HAVING dpth <= 1
ORDER BY node.lft;
 
Old 01-25-2010, 12:12 AM   #3
h/w
Senior Member
 
Registered: Mar 2003
Location: New York, NY
Distribution: Debian Testing
Posts: 1,286

Original Poster
Rep: Reputation: 46
Not being a DBA, I realize this is far from optimal for large datasets. Any suggestions on how to make it so?
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query error frieza Programming 5 03-05-2009 03:18 PM
Sql query issue talat Linux - Server 3 10-18-2007 08:59 AM
SQL Query - sum / group by one year periods smaida Programming 2 03-09-2007 08:10 AM
Error in SQL Query.. gobi_dgm Linux - Software 1 02-03-2007 09:41 AM
Query SQL error gabsik Linux - Software 37 07-22-2006 02:34 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 09:20 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration