Solved

categories and subcategories

Posted on 2009-05-14
6
368 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to insert an Iframe into 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 : Open Page or Post…
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…

729 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