• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

MSSQL Query to display parent, child, grandchild in 1 field for name and 1 field for ids

I have a table called category with the following fields:

category_id  category_name   category_parent
2               Accounting                     0
3               Hardware                       0
4               Printers                       3
5               Monitors                       3
6               Jam                            4
7               Replace Toner               4
8               Finance Pro                   2


There are only 3 levels to this tree.

I am writing an application where there is a Category, Subcategory and a sub-sub-category. My original application design used a combo box (3 drop down menus) to filter the results during data entry. Instead the users are now asking me for a single type ahead field such that when they type "Printer" I would be showing them:

Hardware.Printers
Hardware.Printers.Jam
Hardware.Printers.Replace Toner

as items to click on. When they click on the item I'll not only need the names, but also the ids for my form. So basically I ALSO need to be generating

3,4
3,4,6
3,4,7

I'd like to simply create a view called Category_Typeahead that will feed my typeahead box. I'm basically looking for something like this:

Select * from category_typeahead

category_names                 category_ids    
--------------                                      -------------    
Accounting.Finance Pro            2,8,
Hardware.Printers                                      3,4,
Hardware.Printers.Jam             3,4,6
Harwdware.Printers.Replace Toner        3,4,7

Note: I do not want just the Parent category alone. The hightest level of display is Parent.Child

The winning answer is a simple query I can put in a view that will spit out the required data.
0
parsnip1
Asked:
parsnip1
  • 10
  • 5
1 Solution
 
ralmadaCommented:
Try this:
just replaces 'categories' for your table name

create view category_typeahead
as
with CTE(category_name, category_id, category_parent, category_ids, org_level) as (
select	convert(varchar, a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, a.category_id),
		0
from categories a
where a.category_parent = 0
union all
select	convert(varchar, d.category_name + '.' + a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, d.category_ids + ', ' + cast(a.category_id as varchar)),
		d.org_level + 1
from categories a
inner join CTE d on d.category_id = a.category_parent)
select category_name, category_ids from CTE
where org_level > 0

Open in new window

0
 
ralmadaCommented:
then just run it like this:

select * from category_typeahead
order by category_ids
0
 
parsnip1Author Commented:
Hello Ralmada,

Thanks so much. You got it halfway there. Your view is giving me the ids correctly but not the name. I am only getting the top level name so basically the result I get is:

Category Name            Category IDs
Hardware                      3,4
Hardware                      3,4,6

I need the category name field to be saying:
Hardware.Printer
Hardware.Printer.Jam
                     

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ralmadaCommented:
Have you changed anything from the query? The query I gave you is giving the desired result. I've just run it on my test enviroment and I got:
 

Accounting.Finance Pro	2, 8
Hardware.Printers	3, 4
Hardware.Printers.Jam	3, 4, 6
Hardware.Printers.Replace Tone	3, 4, 7
Hardware.Monitors	3, 5

Open in new window

0
 
parsnip1Author Commented:
just tested again with your exact code on sql20005 (even recreated the view). same result. do you have some sort of option turned on perhaps?
0
 
ralmadaCommented:
This is the entire example I've run in my environment. Give it a try and let me know how it goes.

create table categories (
category_id int, 
category_name varchar(20),
category_parent int)
 
insert categories values(2,               'Accounting',                     0)
insert categories values(3,               'Hardware',                       0)
insert categories values(4,               'Printers',                       3)
insert categories values(5,               'Monitors',                       3)
insert categories values(6,               'Jam',                            4)
insert categories values(7,               'Replace Toner',               4)
insert categories values(8,               'Finance Pro',                   2)
go;
 
create view category_typeahead
as
with CTE(category_name, category_id, category_parent, category_ids, org_level) as (
select	convert(varchar, a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, a.category_id),
		0
from categories a
where a.category_parent = 0
union all
select	convert(varchar, d.category_name + '.' + a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, d.category_ids + ', ' + cast(a.category_id as varchar)),
		d.org_level + 1
from categories a
inner join CTE d on d.category_id = a.category_parent)
select category_name, category_ids from CTE
where org_level > 0
go;
 
select * from category_typeahead 
order by category_ids;

Open in new window

0
 
ralmadaCommented:
I'm also in SQL 2005. Collation: Latin1_General_CI_AS.
Also try changing the way the query is displayed. from "Result to Grid" to "Result to text" as shown below:

resulttotext.jpg
0
 
parsnip1Author Commented:
ok... here's whats going on (and I really apologize for this as I didnt think it would be relevant). I have one last field called category_active, it's a bit. If I remove category_active your view works but if it's there it does not.

I attempted to make a view called "category_active" as follows:
 " create view category_active as select category_id, category_name, category_parent from category where category_active='1'"

I then update your view to point at "category_active" instead of "categories" and it still doesnt work.

Again, I apologize for not including this other field as I didnt think it would affect the query...
0
 
ralmadaCommented:
I don't know how this 'category_active' column might affect the result. Here's an updated query with category_active included. I've tested it and is working too.
create view category_typeahead
as
with CTE(category_name, category_id, category_parent, category_ids, category_active, org_level) as (
select	convert(varchar, a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, a.category_id),
		a.category_active,
		0
from category a
where a.category_parent = 0
union all
select	convert(varchar, d.category_name + '.' + a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, d.category_ids + ', ' + cast(a.category_id as varchar)),
		a.category_active,
		d.org_level + 1
from category a
inner join CTE d on d.category_id = a.category_parent)
select category_name, category_ids, category_active from CTE
where org_level > 0

Open in new window

0
 
parsnip1Author Commented:
First and foremost I can't thank you enough for all your help. All the same: it's just not working.

By the way, I don't need to display the category_active field (in fact I prefer it not appear). I just need to make sure that no entries are generated for something that has category_active='0'.

No clue why this isnt working.
screenshot.jpg
0
 
ralmadaCommented:
Try this, I've added a length of 500 to the category_name column.
If this doesn't work can you tell me what collation are you using?

create view category_typeahead
as
with CTE(category_name, category_id, category_parent, category_ids, org_level) as (
select	convert(varchar(500), a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, a.category_id),
		0
from category a
where a.category_parent = 0
union all
select	convert(varchar(500), d.category_name + '.' + a.category_name), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, d.category_ids + ', ' + cast(a.category_id as varchar)),
		d.org_level + 1
from category a
inner join CTE d on d.category_id = a.category_parent)
select category_name, category_ids, category_active from CTE
where org_level > 0

Open in new window

0
 
ralmadaCommented:
Also can you post the exact code you are executing and the exact table definition?
0
 
ralmadaCommented:
Also try changing the '.'  in line 12 to something like '->' to see what could be causing this.
select convert(varchar(500), d.category_name + '->' + a.category_name),
 
0
 
parsnip1Author Commented:
Ok... almost there. The query you paster above (24184667) returns an error about category_active not appearing in CTE so I just took that out of there. Ran the query and it's close... it shows the fields albeit very far apart (see attached screenshot)

I tried doing a little rtrim'ing but didnt have much luck.

Table defs are as follows:
CREATE TABLE [dbo].[category](
      [category_id] [int] IDENTITY(1,1) NOT NULL,
      [category_name] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [category_parent] [int] NOT NULL,
      [category_active] [bit] NOT NULL CONSTRAINT [DF_category_category_active]  DEFAULT ('True'),
 CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED
(
      [category_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
screen2.JPG
0
 
ralmadaCommented:
Give this a shot:
Please note that I've added a filter (where category_active = '1') because you've mentioned that you wanted to display only current categories.

create view category_typeahead
as
with CTE(category_name, category_id, category_parent, category_ids, org_level) as (
select	rtrim(convert(varchar(500), a.category_name)), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, a.category_id),
		0
from category a
where a.category_parent = 0
union all
select	rtrim(convert(varchar(500), d.category_name + '.' + a.category_name)), 
		a.category_id, 
		a.category_parent, 
		convert(varchar, d.category_ids + ', ' + cast(a.category_id as varchar)),
		d.org_level + 1
from category a
inner join CTE d on d.category_id = a.category_parent
where category_active = '1'
)
select category_name, category_ids from CTE
where org_level > 0

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now