return correct sub_id in select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

return correct sub_id in select query

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-24 : 05:20:24

Hi,

I have 2 tables in the database that stores categories and subcategories

USE [ecrmKB]
GO
/****** Object: Table [dbo].[tb_knbCategorySub] Script Date: 09/24/2008 11:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_knbCategorySub](
[CSu_id] [int] IDENTITY(1,1) NOT NULL,
[CSu_parentCatID] [int] NOT NULL,
[CSu_childCatID] [int] NOT NULL,
CONSTRAINT [PK_tb_knbCategorySub] PRIMARY KEY NONCLUSTERED
(
[CSu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tb_knbCategorySub] WITH CHECK ADD CONSTRAINT [FK_tb_knbCategorySub_tb_knbCategory] FOREIGN KEY([CSu_parentCatID])
REFERENCES [dbo].[tb_knbCategory] ([Cat_id])
GO
ALTER TABLE [dbo].[tb_knbCategorySub] CHECK CONSTRAINT [FK_tb_knbCategorySub_tb_knbCategory]
GO
ALTER TABLE [dbo].[tb_knbCategorySub] WITH CHECK ADD CONSTRAINT [FK_tb_knbCategorySub_tb_knbCategory1] FOREIGN KEY([CSu_childCatID])
REFERENCES [dbo].[tb_knbCategory] ([Cat_id])
GO
ALTER TABLE [dbo].[tb_knbCategorySub] CHECK CONSTRAINT [FK_tb_knbCategorySub_tb_knbCategory1]

GO
/****** Object: Table [dbo].[tb_knbCategory] Script Date: 09/24/2008 11:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_knbCategory](
[Cat_id] [int] IDENTITY(1000,1) NOT NULL,
[Cat_name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cat_description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cat_status] [int] NULL CONSTRAINT [DF_tb_knbArticleCat_ACa_status] DEFAULT ((1)),
[Cat_clientID] [int] NULL,
[Cat_kbID] [int] NULL,
[Cat_order] [int] NULL CONSTRAINT [DF_tb_knbCategory_Cat_order] DEFAULT ((0)),
CONSTRAINT [PK_tb_knbArticleCat] PRIMARY KEY NONCLUSTERED
(
[Cat_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tb_knbCategory] WITH CHECK ADD CONSTRAINT [FK_tb_knbCategory_tb_knbCategoryStatus] FOREIGN KEY([Cat_status])
REFERENCES [dbo].[tb_knbCategoryStatus] ([CSt_id])
GO
ALTER TABLE [dbo].[tb_knbCategory] CHECK CONSTRAINT [FK_tb_knbCategory_tb_knbCategoryStatus]
GO
ALTER TABLE [dbo].[tb_knbCategory] WITH CHECK ADD CONSTRAINT [FK_tb_knbCategory_tb_knbKnowledgeBase] FOREIGN KEY([Cat_kbID])
REFERENCES [dbo].[tb_knbKnowledgeBase] ([KBa_id])
GO
ALTER TABLE [dbo].[tb_knbCategory] CHECK CONSTRAINT [FK_tb_knbCategory_tb_knbKnowledgeBase]

Data is stored in the tables as follows:

tb_knbCategory

Cat_id Cat_name Cat_description Cat_status Cat_clientID Cat_kbID Cat_order
1000 exam NULL 1 12 1 0
1001 70-431 NULL 1 12 1 0
1002 exam NULL 1 12 1 0
1003 exam NULL 1 12 1 0
1004 exam NULL 1 12 1 0
1005 70-536 NULL 1 12 1 0
1007 test NULL 1 12 1 0
1006 test NULL 1 12 1 0
1008 1 NULL 1 12 1 0
NULL NULL NULL NULL NULL NULL NULL

tb_knbCategorySub

CSu_id CSu_parentCatID CSu_childCatID
1 1000 1001
2 1001 1004
3 1002 1005
4 1005 1006
5 1003 1007
6 1006 1008
NULL NULL NULL

Now a user will sent to the data to the select query in the following form: exam\70-536\test or exam\70-536 or exam\test or HP\test, HP\test \1\2\ and so forth. The numbers of child levels are unknown.

I need to return the correct id according to the data path I receive so ‘exam\70-536\test’ should return the id: 1006 for test.

How can I go about doing this?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 05:25:05

Search for recursive CTEs within forum. you will get lots of examples.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-24 : 07:58:44

can someone please help me write the recursive query? I tried a few times. Also how do i sent to the query the value (path) such as 'exam\70-536\test '?

thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 08:09:22

quote:
Originally posted by collie

can someone please help me write the recursive query? I tried a few times. Also how do i sent to the query the value (path) such as 'exam\70-536\test '?

thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

something like

;With Cat_CTE (Cat_id,Cat_name, Cat_description,Path,Level)AS
(SELECT c.Cat_id,c.Cat_name,c.Cat_description,NULL,0
FROM  [dbo].[tb_knbCategory] c
LEFT JOIN  [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
WHERE cs.CSu_parentCatID IS NULL

UNION ALL

SELECT c.Cat_id,c.Cat_name,c.Cat_description,
COALESCE(cc.Path+'/','')+c.Cat_name,
cc.Level +1 
FROM  [dbo].[tb_knbCategory] c
INNER JOIN  [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
INNER JOIN Cat_CTE cc
ON cc.Cat_id=cs.CSu_childCatID
)

SELECT * FROM Cat_CTE WHERE Path =@Path

@Path is passed on value (exam\70-536\test)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-24 : 08:28:54

Thanks for the help.

I tried to run the query and it says that Path is an integer field:
Conversion failed when converting the varchar value 'exam\70-536\test' to data type int.

I am trying to figure out why.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 08:36:20

quote:
Originally posted by collie

Thanks for the help.

I tried to run the query and it says that Path is an integer field:
Conversion failed when converting the varchar value 'exam\70-536\test' to data type int.

I am trying to figure out why.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

;With Cat_CTE (Cat_id,Cat_name, Cat_description,Path,Level)AS
(SELECT c.Cat_id,c.Cat_name,c.Cat_description,CAST(NULL AS varchar(1000)),0
FROM  [dbo].[tb_knbCategory] c
LEFT JOIN  [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
WHERE cs.CSu_parentCatID IS NULL

UNION ALL

SELECT c.Cat_id,c.Cat_name,c.Cat_description,
COALESCE(cc.Path+'/','')+c.Cat_name,
cc.Level +1 
FROM  [dbo].[tb_knbCategory] c
INNER JOIN  [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
INNER JOIN Cat_CTE cc
ON cc.Cat_id=cs.CSu_childCatID
)

SELECT * FROM Cat_CTE WHERE Path =@Path

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-24 : 09:07:28

Thanks

I get the error:
Types don't match between the anchor and the recursive part in column "Path" of recursive query "Cat_CTE".

I tried casting path to nvarchar in the line 'With Cat_CTE (Cat_id,Cat_name, Cat_description,Path ,Level)AS'
but it also raises an error.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 10:52:16

quote:
Originally posted by collie

Thanks

I get the error:
Types don't match between the anchor and the recursive part in column "Path" of recursive query "Cat_CTE".

I tried casting path to nvarchar in the line 'With Cat_CTE (Cat_id,Cat_name, Cat_description,Path ,Level)AS'
but it also raises an error.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

try casting it in recursive part as well.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-24 : 11:15:08

hi,

okay i changed it but when i run it in query analyser i dont get any results.

;With Cat_CTE (Cat_id,Cat_name, Cat_description,Path,Level)AS
(SELECT c.Cat_id,c.Cat_name,c.Cat_description,CAST(NULL AS varchar(1000)),0
FROM [dbo].[tb_knbCategory] c
LEFT JOIN [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
WHERE cs.CSu_parentCatID IS NULL

UNION ALL

SELECT c.Cat_id,c.Cat_name,c.Cat_description,

CAST (COALESCE(cc.path +'/' ,'') +c.Cat_name AS varchar(1000))
,

cc.Level +1
FROM [dbo].[tb_knbCategory] c
INNER JOIN [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
INNER JOIN Cat_CTE cc
ON cc.Cat_id=cs.CSu_childCatID
)

SELECT * FROM Cat_CTE WHERE Path ='exam/70-536/test'

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 11:29:21

can i assume that you will not have any records on table [dbo].[tb_knbCategorySub] for categories which there are no child categories? then i think below should work.

;With Cat_CTE (Cat_id,Cat_name, Cat_description,Path,Level)AS
(SELECT c.Cat_id,c.Cat_name,c.Cat_description,CAST(NULL AS varchar(1000)),0
FROM [dbo].[tb_knbCategory] c
LEFT JOIN [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
WHERE cs.CSu_parentCatID IS NULL

UNION ALL

SELECT c.Cat_id,c.Cat_name,c.Cat_description,

CAST (c.Cat_name + COALESCE('/' +cc.path ,'')  AS varchar(1000))
,

cc.Level +1 
FROM [dbo].[tb_knbCategory] c
INNER JOIN [dbo].[tb_knbCategorySub] cs
ON cs.CSu_parentCatID=c.Cat_id
INNER JOIN Cat_CTE cc
ON cc.Cat_id=cs.CSu_childCatID
)

SELECT * FROM Cat_CTE WHERE Path ='exam/70-536/test'

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-25 : 03:51:36

thanks.

i ran the query and it returns the id 1002 for exam and child id 1005 for 70-536. How can i get the value for test? I actually need the id for the last category name in the path. Example a/b/c/d then I need the cat_id for d.

I tried a few options but nothing works :-(

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-25 : 04:03:35

Hi,

Another thing I noticed is that if i just send the path 'exam/70-536' then the query doesn't return anything and i want it to return 70-536.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 04:22:24

quote:
Originally posted by collie

Hi,

Another thing I noticed is that if i just send the path 'exam/70-536' then the query doesn't return anything and i want it to return 70-536.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

the query i gave starts with child node and goes upwards till it reaches the root.what does below return?


SELECT * FROM Cat_CTE WHERE Path LIKE '%test'

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-25 : 05:18:59

hi,

this is the result:
1006 test NULL test 1 1008
1005 70-536 NULL 70-536/test 2 1006
1002 exam NULL exam/70-536/test 3 1005

This could be fine however, i could also have the following path:
hp/print/test so i need to return the correct cat_id for the test in this path.
Also, if I have more than 3 levels such as exam/70-536/test/one then nothing is returned- i also trued using LIKE '%one'

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:23:05

quote:
Originally posted by collie

hi,

this is the result:
1006 test NULL test 1 1008
1005 70-536 NULL 70-536/test 2 1006
1002 exam NULL exam/70-536/test 3 1005

This could be fine however, i could also have the following path:
hp/print/test so i need to return the correct cat_id for the test in this path.
Also, if I have more than 3 levels such as exam/70-536/test/one then nothing is returned- i also trued using LIKE '%one'

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

what are getting as Path for record with categgory name test?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-25 : 05:33:40

i am only getting the path for 'exam/70-536/test' and not for the other path where test is also a 3rd level: top1/subtop1/test.

This is the result:
cat_id cat_name cat_desc path level child_id
1006 test NULL test 1 1008
1005 70-536 NULL 70-536/test 2 1006
1002 exam NULL exam/70-536/test 3 1005

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:36:03

quote:
Originally posted by collie

i am only getting the path for 'exam/70-536/test' and not for the other path where test is also a 3rd level: top1/subtop1/test.

This is the result:
cat_id cat_name cat_desc path level child_id
1006 test NULL test 1 1008
1005 70-536 NULL 70-536/test 2 1006
1002 exam NULL exam/70-536/test 3 1005

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

ok do you have a level which can be distingusihed as root level (having no more parents)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-09-25 : 05:42:44

Thanks for your time and patience. I really appreciate it.

The answer to your question is no.

Here is the data from the tables:

SELECT [Cat_id]
,[Cat_name]
FROM [ecrmKB].[dbo].[tb_knbCategory]

1000 exam
1001 70-431
1002 exam
1003 exam
1004 exam
1009 top1
1011 test
1005 70-536
1007 test
1006 test
1008 one
1010 subtop1

SELECT [CSu_id]
,[CSu_parentCatID]
,[CSu_childCatID]
FROM [ecrmKB].[dbo].[tb_knbCategorySub]

1 1000 1001
2 1001 1004
3 1002 1005
4 1005 1006
8 1010 1011
5 1003 1007
6 1006 1008
7 1009 1010

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources