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-05-25 : 05:25:09

Hi Guys,

Please help this one

I have two table LP_Category and LP_Expertation

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 1 BE 0 student
2 4 3 BE 0 student
3 5 3 BE 0 student

We want display the record Category(count of Experts)

+ Sports(1)
+ Music(2)
+ Test(0)

This is my test query

select CatId,ParentID,
(Name +'('+ (select count(*) FROM LP_Expertation WHERE sc.CatId=CatId) +')'+) CategoryName
FROM LP_Category sc where parentID=0

Regards
Sambath kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-25 : 05:37:33

[code]SELECT c.Name + '(' + CAST(COUNT(e.CatID) AS VARCHAR(12)) + ')'
FROM LP_Category AS c
LEFT JOIN LP_Expertation AS e ON e.CatID = c.CatID
WHERE c.ParentID = 0
GROUP BY c.Name[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 05:38:17

select c.catid,c.parentid,c.name + ' ( '+cast(ISNULL(cnt,0) as varchar(12))+' )'
from @LP_Category c
left join (select catid, count(catid)as cnt from @LP_Expertation group by catid) e on c.catid= e.catid
where c.parentid = 0

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-05-25 : 07:39:16

dear Peso,

Thanks for your time.

The query is working in our code thank you

Regards
sambath

quote:

Originally posted by Peso

SELECT c.Name + '(' + CAST(COUNT(e.CatID) AS VARCHAR(12)) + ')'
FROM LP_Category AS c
LEFT JOIN LP_Expertation AS e ON e.CatID = c.CatID
WHERE c.ParentID = 0
GROUP BY c.Name

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources