?
Solved

categories and subcategories

Posted on 2009-05-14
6
Medium Priority
?
369 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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
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…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
Suggested Courses

764 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