recursive query - SQL Server Forums

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)

recursive query

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-06 : 11:11:14

Hi

I have the following query that needs to be tweaked so that I can filter based on a CategoryID, I could really need some help with this.


SELECT     dbo.ArticlesInCategories.CategoryId, dbo.ArticlesInCategories.ArticleId, dbo.Article.Description, dbo.Article.Name
FROM         dbo.ArticlesInCategories INNER JOIN
                      dbo.Article ON dbo.ArticlesInCategories.ArticleId = dbo.Article.ArticleId

I need to find all article's that are placed in that category.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 11:18:21

The subject line threw me off a little bit. Wouldn't the following do the filtering on CategoryId?


declare @categoryId int; 
set @categoryId = 11;

SELECT    
	 dbo.ArticlesInCategories.CategoryId, 
	 dbo.ArticlesInCategories.ArticleId, 
	 dbo.Article.Description, 
	 dbo.Article.Name
FROM         
	dbo.ArticlesInCategories 
	INNER JOIN dbo.Article 
		ON dbo.ArticlesInCategories.ArticleId = dbo.Article.ArticleId
where
	dbo.ArticlesInCategories.CategoryId = @categoryId

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-06 : 11:45:42

Hi

Sorry for poor explanation, yes that would filter on one categoryid, the thing is that it work like a tree structure. A category can have multiple categories and articles in itself. And I need to find all articles that are placed in that "top category". Hope that helps.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 12:01:34

Do you have a Category table (tables) that store the categories and the relationships between parent and child categories? Can you post the DDL for those table(s)?

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-06 : 13:13:08

[code]

CREATE TABLE [dbo].[Category](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[Parent] [int] NULL,
[Name] [varchar](50) NOT NULL,
[SortOrder] [int] NOT NULL,
[IsEnabled] [bit] NOT NULL,
[MediabankId] [int] NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ArticlesInCategories](
[ArticlesInCategoriesId] [int] IDENTITY(1,1) NOT NULL,
[ArticleId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
CONSTRAINT [PK_ArticlesInCategories] PRIMARY KEY CLUSTERED
(
[ArticlesInCategoriesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Article](
[ArticleId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[Description] [varchar](1000) NOT NULL,
[ArtNo] [varchar](255) NULL,
[Name] [varchar](255) NULL,
[PublishDateFormatted] AS (CONVERT([nvarchar](20),replace(CONVERT([nvarchar](20),[PublishDate],(102)),'.','-'),(0))),
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ArticleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[/code]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 13:29:13

The following code parses - but not tested with data. If it doesn't work, can you post some sample data?


declare @categoryId int; 
set @categoryId = 11;

;with CategoryCTE as
(
   select [CategoryId], [Parent]   --- Category and Parent to start with
   from Category 
   where [CategoryId] = @categoryId
   
   union all
   
   select [CategoryId], [Parent]  --- descendants of @categoryId
   from Category c inner join CategoryCTE cc on cc.[CategoryId] = c.[Parent]
)
SELECT    
    dbo.ArticlesInCategories.CategoryId, 
    dbo.ArticlesInCategories.ArticleId, 
    dbo.Article.Description, 
    dbo.Article.Name
FROM         
   dbo.ArticlesInCategories 
   INNER JOIN dbo.Article 
      ON dbo.ArticlesInCategories.ArticleId = dbo.Article.ArticleId
   INNER JOIN CategoryCTE
      ON CategoryCTE.[CategoryId] = dbo.ArticlesInCategories.CategoryId

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-06 : 14:16:26

I get this error when I try to run it..

Msg 209, Level 16, State 1, Line 12
Ambiguous column name 'CategoryId'.
Msg 209, Level 16, State 1, Line 12
Ambiguous column name 'Parent'.

try this sample data.


INSERT INTO Category
           ([Parent]
           ,[Name])
     VALUES
           (NULL
           ,'TopNode')

INSERT INTO Category
           ([Parent]
           ,[Name])
     VALUES
           (1
           ,'Subnode 1')

INSERT INTO Category
           ([Parent]
           ,[Name])
     VALUES
           (1
           ,'Subnode 2')


INSERT INTO [dbo].[ArticlesInCategories]
           ([ArticleId]
           ,[CategoryId])
     VALUES
           (1
           ,1)

INSERT INTO [dbo].[ArticlesInCategories]
           ([ArticleId]
           ,[CategoryId])
     VALUES
           (2
           ,1)

INSERT INTO [dbo].[ArticlesInCategories]
           ([ArticleId]
           ,[CategoryId])
     VALUES
           (3
           ,2)

INSERT INTO [dbo].[ArticlesInCategories]
           ([ArticleId]
           ,[CategoryId])
     VALUES
           (4
           ,3)

INSERT INTO [dbo].[Article]
           ([CustomerId]
           ,[Description]
           ,[ArtNo]
           ,[Name]
)
     VALUES
           (1
           ,'article 1'
           ,'111'
           ,'name 1'
)

INSERT INTO [dbo].[Article]
           ([CustomerId]
           ,[Description]
           ,[ArtNo]
           ,[Name]
)
     VALUES
           (1
           ,'article 2'
           ,'222'
           ,'name 2'
)

INSERT INTO [dbo].[Article]
           ([CustomerId]
           ,[Description]
           ,[ArtNo]
           ,[Name]
)
     VALUES
           (1
           ,'article 3'
           ,'333'
           ,'name 3'
)

INSERT INTO [dbo].[Article]
           ([CustomerId]
           ,[Description]
           ,[ArtNo]
           ,[Name]
)
     VALUES
           (1
           ,'article 4'
           ,'444'
           ,'name 4'
)

INSERT INTO [dbo].[Article]
           ([CustomerId]
           ,[Description]
           ,[ArtNo]
           ,[Name]
)
     VALUES
           (1
           ,'article 5'
           ,'555'
           ,'name 5'
)

INSERT INTO [dbo].[Article]
           ([CustomerId]
           ,[Description]
           ,[ArtNo]
           ,[Name]
)
     VALUES
           (1
           ,'article 6'
           ,'666'
           ,'name 6'
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 14:25:17

Ooops! My bad!! The column names need to be qualified with the table alias.

declare @categoryId int; 
set @categoryId = 11;

;with CategoryCTE as
(
   select [CategoryId], [Parent]   --- Category and Parent to start with
   from Category 
   where [CategoryId] = @categoryId
   
   union all
   
   select c.[CategoryId], c.[Parent]  --- descendants of @categoryId
   from Category c inner join CategoryCTE cc on cc.[CategoryId] = c.[Parent]
)
SELECT    
    dbo.ArticlesInCategories.CategoryId, 
    dbo.ArticlesInCategories.ArticleId, 
    dbo.Article.Description, 
    dbo.Article.Name
FROM         
   dbo.ArticlesInCategories 
   INNER JOIN dbo.Article 
      ON dbo.ArticlesInCategories.ArticleId = dbo.Article.ArticleId
   INNER JOIN CategoryCTE
      ON CategoryCTE.[CategoryId] = dbo.ArticlesInCategories.CategoryId

I couldn't use the test data. It complained about not being able to insert NULL into column 'MediabankId'.

If this does not work, I promise, I will stop posting untested code and will post only after testing it using your test data

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-06 : 15:23:13

Worked great, Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources