[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

categories and subcategories

Posted on 2009-05-14
6
Medium Priority
?
370 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to Import and export files in WordPress. 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 : Click on Too…
Suggested Courses

650 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