asked on

categories and subcategories

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

boodyguard

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 ?

usachrisk1983

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

Coast Line

ASKER

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

Coast Line

ASKER

my every approach is going wromg, i tried like this:<input id="gwProxy" type="hidden"><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

Coast Line

ASKER

i tried something like this:

here is my sdmenu
<input id="gwProxy" type="hidden"><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

ASKER CERTIFIED SOLUTION

usachrisk1983

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial