select 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

General SQL Server Forums

New to SQL Server Programming

select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-03 : 08:44:41

Hi Experts,

Please help this one

We have three tables LP_Usermanagement,LP_Category and LP_Expertation

UserId Username password
3 sam sam123
4 kathi 123456
5 Kamal 123456

CatId Name Parentid Status
1 Sports 0 1
2 Test 0 1
3 Music 0 1
4 Football 1 1
5 Vollyball 1 1

ExpertId UserId CatId Degree Experience Qualifications
1 3 4 BE 0 student
2 4 5 BE 0 student
3 5 5 BE 0 student

We want display the Expert information

- Sports
- Football(1)
- Vollyball(2)
+ Music
+ Test

output

1. When I click Football category 1 Expert Info disply
2. When I click Vollyball category 2 Expert info disply
3. When I click Sports to dispaly 3 Expert info (Please help this problem)

This is my test query

Select U.*,E.*,C.*
From LP_Usermanagement U inner join LP_ExpertationLanguage E ON U.UserId=E.UserId
inner join LP_Category C ON E.CatId=C.CatId
Where C.ParentId=1

Regards
Sambath kumar

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-06-03 : 09:43:18

I am not really sure what you are doing, but with your Where clause restricting ParentID to 1, your ParentID in table C = 1 for football and volleyball, but is 0 for sports and the others. So you won't get anything for sports in that Select statement.

Duane

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-03 : 11:17:50

Hi Duane,

Thanks for your Reply,

I will explain it again,

We have three tables LP_Usermanagement,LP_Category and LP_Expertation

Please find the sameple data for each tables

LP_Usermanagement
=================
UserId Username
2 Dhina
3 sam
4 kathi
5 Kamal
6 Kotti

LP_Category
===========
CatId Name Parentid
1 Sports 0
2 Test 0
3 Music 0
4 Football 1
5 Vollyball 1

LP_Expertation
===============
Id UserId CatId
1 3 4
2 4 5
3 5 5
4 2 1
5 6 3
Explanation :

Here the category is SPORTS and Sub category of Suports are Football and Vollyball.... I am using Parent key concept

When i used to find the users based on the category (LP_Category ) i have to display all the users who are in the same category and subcategory,

it means suppose i am trying to find the users in the category "SPORTS" Means i have to display all the users who are in "SPORTS", "VOLLYBALL", and "FOOTBALL"...

It should consider the CATEGORY AND all the SUBCATEGORY..

Example :

FOR "SPORTS" Category i have to display the below OUTPUT

Category Username
--------------------
SPORTS DHINA
FOOTBALL SAM
VOLLEYBALL KATHI
VOLLEYBALL KAMAL

SPORTS IS THE MAIN CATEGORY AND VOLLEYBALL AND FOOTBALL ARE THE SUBCATEGORY OF SPORTS

Please help me to get my expected output,

Please let me know if you need more clarification

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 12:06:56

[code]
;With Category_Hierarchy (CategoryID,Category,Username) AS
(SELECT c.CatID,c.Name,u.Username
FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
WHERE c.Name=@Category

UNION ALL

FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
INNER JOIN Category_Hierarchy ch
ON c.ParentID=ch.CategoryID
)

SELECT Category,Username
FROM Category_Hierarchy
ORDER BY CategoryID

OPTION (MAXRECURSION 0)
[/code]

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-04 : 00:39:01

Hi visakh16,

Thanks for your Reply,

How to execute the query. I got error like this

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Server: Msg 137, Level 15, State 1, Line 8
Must declare the variable '@Category'.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'MAXRECURSION'.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 00:49:41

are you using SQL Server 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-04 : 03:16:04

Hi Khan

We have try this query Sql 2000 and Sql2005

With Category_Hierarchy (CategoryID,Category,Username) AS
(SELECT c.CatID,c.Name,u.Username
FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
WHERE c.CatId=1

UNION ALL

FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
INNER JOIN Category_Hierarchy ch
ON c.ParentID=ch.CategoryID
)

SELECT Category,Username
FROM Category_Hierarchy
ORDER BY CategoryID

OPTION (MAXRECURSION 0)

I got error like this

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'FROM'.
Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'MAXRECURSION'.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 03:33:06

it will not work in SQL 2000. CTE is not available in SQL 2000.
you missed a ';' before with

quote:
Originally posted by esambath

Hi Khan

We have try this query Sql 2000 and Sql2005

;With Category_Hierarchy (CategoryID,Category,Username) AS
(SELECT c.CatID,c.Name,u.Username
FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
WHERE c.CatId=1

UNION ALL

FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
INNER JOIN Category_Hierarchy ch
ON c.ParentID=ch.CategoryID
)

SELECT Category,Username
FROM Category_Hierarchy
ORDER BY CategoryID

OPTION (MAXRECURSION 0)

I got error like this

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'FROM'.
Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'MAXRECURSION'.

Thanks in advance

KH
[spoiler]Time is always against us[/spoiler]

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-04 : 05:26:46

Hi khtan

Thanks for your Reply,

We have try this query Sql2005

;With Category_Hierarchy (CategoryID,Category,Username) AS
(SELECT c.CatID,c.Name,u.Username
FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
WHERE c.CatId=1

UNION ALL

FROM LSP_Category c
INNER JOIN LP_Expertation e
ON e.CatId=c.CatId
INNER JOIN LP_Usermanagement u
ON u.UserId=e.UserId
INNER JOIN Category_Hierarchy ch
ON c.ParentID=ch.CategoryID
)

SELECT Category,Username
FROM Category_Hierarchy
ORDER BY CategoryID

OPTION (MAXRECURSION 0)

I got error like this

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'FROM'.
Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'MAXRECURSION'.

Please help this concern

Thanks in advance

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-06-04 : 05:58:28

Hi

declare @LP_Usermanagement table (userid int,username varchar(20))

insert into @LP_Usermanagement values(2, 'Dhina')
insert into @LP_Usermanagement values(3, 'sam')
insert into @LP_Usermanagement values(4, 'kathi')
insert into @LP_Usermanagement values(5, 'Kamal')
insert into @LP_Usermanagement values(6, 'Kotti')

--select * from @LP_Usermanagement

declare @LP_Category table (CatId int,Name varchar(20),parentid int)

insert into @LP_Category values(1, 'Sports',0)
insert into @LP_Category values(2, 'Test',0)
insert into @LP_Category values(3, 'Music',0)
insert into @LP_Category values(4, 'Football',1)
insert into @LP_Category values(5, 'Vollyball',1)

--select * from @LP_Category

declare @LP_Expertation table (id int,userid int, CatId int)

insert into @LP_Expertation values(1, 3,4)
insert into @LP_Expertation values(2, 4,5)
insert into @LP_Expertation values(3, 5,5)
insert into @LP_Expertation values(4, 2,1)
insert into @LP_Expertation values(5, 6,3)

--select * from @LP_Expertation

declare @catid int

select @catid=catid from @LP_Category where name='sports'

select username,name from @LP_Usermanagement u,@LP_Expertation e,@LP_Category c
where u.userid=e.userid and c.catid=e.catid
and c.catid in
(
select catid from @LP_Category where catid=@catid or parentid=@catid)

Kunal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 06:14:28

try this. Visakh missed out the SELECT statement from the 2nd query of the UNION


;WITH Category_Hierarchy (CategoryID,Category,Username) 
AS
(
	SELECT 		c.CatID,
			c.Name,
			u.Username
	FROM 		LSP_Category c
	INNER JOIN 	LP_Expertation e	ON e.CatId	= c.CatId
	INNER JOIN 	LP_Usermanagement u	ON u.UserId	= e.UserId
	WHERE 		c.Name = @Category

UNION ALL

	SELECT 		c.CatID,
			c.Name,
			u.Username
	FROM 		LSP_Category c
	INNER JOIN 	LP_Expertation e	ON e.CatId	= c.CatId
	INNER JOIN 	LP_Usermanagement u	ON u.UserId	= e.UserId
	INNER JOIN 	Category_Hierarchy ch	ON c.ParentID	= ch.CategoryID
)
SELECT 	Category,
	Username
FROM 	Category_Hierarchy 
ORDER BY CategoryID
OPTION (MAXRECURSION 0)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources