Solved

categories and subcategories

Posted on 2009-05-14
6
367 Views
Last Modified: 2013-12-24
i have a table with catID, parentID, catname

i have relationship in table which is nested very deep. but i want to show to the end user, only one level deep.

like if parentID is 0, another subcategory has parentID of 1. that's it, no more level deep. can experts here tell me that my query is right or wrong, if right, then it is not fetching right results:


SELECT c1.catID, c1.catvisibility, c1.catName, c2.catID AS SubCatID, c2.catName AS SubCategory
        FROM Categories c1 LEFT OUTER JOIN Categories c2 ON c1.catID = c2.ParentID
ORDER BY c1.catName, c1.catID, c2.catName, c2.catID

Open in new window

0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:boodyguard
ID: 24389948
Hi,

Not so easy without seeing your db. But if I undertand correctly,PARENT_ID=0 is your root.
 You want to select only your root level ?
Or you want to select your root level and first sublevel ?
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 24391887
Your query is going to select all categories, even if they are not at the root.  The query below should select only records that are either at the root (parentID = 0) or are children of the first level of the parent.

If instead of 0 you have null as the parentID of root level cateogires, use "where parent.parentid is null" instead of "= 0".  If you want to only include parents when there are children, change the "left join" to just "join".

select parent.catID
     , parent.catVisibility
     , parent.catName
     , children.catID as subCatID
     , children.catName as subCategory
  from categories parent
  left join categories children
    on children.parentID = parent.catid
 where parent.parentid = 0

Open in new window

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24392332
Thanks It works. but only one issue.

When i try to get it by separing parents and its one level deep subcategories. it does not show up all categories.

i tried something like this:

i tried to use the group attribute so that it show relevant subcategories under its main categories, but it shows only a single record. the image is attched with visual presentation. if i omit group. that image is also attached. please check if i am doing something wrong.


<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<script type="text/javascript">
// <![CDATA[
var myMenu;
myMenu.speed = 3;                     // Menu sliding speed (1 - 5 recomended)
myMenu.remember = true;               // Store menu states (expanded or collapsed) in cookie and restore later
myMenu.oneSmOnly = true;             // One expanded submenu at a time
myMenu.markCurrent = true;            // Mark current link / page (link.href == location.href)
window.onload = function() {
myMenu = new SDMenu("my_menu");
myMenu.init();
};
// ]]>
</script>
</head>
 
<body>
<div id="my_menu" class="sdmenu">
<cfoutput query="show" group="catName">
<div>
<span>#CATNAME#</span>
<cfif subcatID IS "">
<cfelse>
<a href="">#SUBCATEGORY#</a>
</cfif>
</div>
</cfoutput>
</body>
</html>

Open in new window

9000.GIF
9001.GIF
0
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24393363
my every approach is going wromg, i tried like this:<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<div id="my_menu" class="sdmenu">
<cfloop query="show">
<cfoutput>
<div>
<span>#catName#</span>
<a href="http://tools.dynamicdrive.com/imageoptimizer/">#subcategory#</a>
</div>
</cfoutput>
</cfloop>
</div>

Open in new window

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24393392
i tried something like this:

here is my sdmenu
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<div id="my_menu" class="sdmenu">
<cfoutput query="show" group="catName">
<div>
<span>#catName#</span>
<a href="http://tools.dynamicdrive.com/imageoptimizer/">#subcategory#</a>
</div>
</cfoutput>
</div>
 
only single one record under a category. while i have 10 subcategories records under 1 category
 
 
div.sdmenu {
	width: 150px;
	font-family: Arial, sans-serif;
	font-size: 12px;
	padding-bottom: 10px;
	background: url(bottom.gif) no-repeat  right bottom;
	color: #fff;
}
div.sdmenu div {
	background: url(title.gif) repeat-x;
	overflow: hidden;
}
div.sdmenu div:first-child {
	background: url(toptitle.gif) no-repeat;
}
div.sdmenu div.collapsed {
	height: 25px;
}
div.sdmenu div span {
	display: block;
	padding: 5px 25px;
	font-weight: bold;
	color: white;
	background: url(expanded.gif) no-repeat 10px center;
	cursor: default;
	border-bottom: 1px solid #ddd;
}
div.sdmenu div.collapsed span {
	background-image: url(collapsed.gif);
}
div.sdmenu div a {
	padding: 5px 10px;
	background: #eee;
	display: block;
	border-bottom: 1px solid #ddd;
	color: #066;
}
div.sdmenu div a.current {
	background : #ccc;
}
div.sdmenu div a:hover {
	background : #066 url(linkarrow.gif) no-repeat right center;
	color: #fff;
	text-decoration: none;
}

Open in new window

0
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 500 total points
ID: 24394482
When you use the GROUP parameter inside a CFOUTPUT tag, there needs to be a nested CFOUTPUT tag so that it knows what to do with the subcategories.  You'll also need to ORDER your SQL statement by the catName for this to work, so add:

order by parent.catName asc

to your SQL statement.  You can do a secondary order on the subcategory - but you don't need to.  I don't have CF running in front of me, but try something like the code snippet (once your SQL is updated).  

What's happening there is that it'll output the catName, and then loop over the subcategories that have that catName and output the subcategory.  For this to work your query muts be ordered by the catName, otherwise you'll get multiple catNames if they're out of order.

<body>
<div id="my_menu" class="sdmenu">
  <cfoutput query="show" group="catName">
     <div>
       <span>#catName#</span>
       <cfoutput>
          <a href="">#show.subcategory#</a><br>
       </cfoutput>
     </div> 
  </cfoutput>
</div>
</body>

Open in new window

0

Featured Post

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Grunt No Clean Targets 6 240
Gulp not seeing Changes 4 96
app server have enough resources... 2 62
Coldfusion cfscript DELETE function 7 38
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question