Help in a qeury includes Count

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 2008 Forums

Transact-SQL (2008)

Help in a qeury includes Count

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2011-10-19 : 03:43:14

Hi
I have 3 tables:
aspnet_Profile (UserID,PropertyValuesString, ...)
RawNews(NewsID,UserID,Title,Text,...)
Images(ImageID,NewsID,Image)

i want a query that returns NewsID,Title,Text,PropertyValuesString,Count(ImageID)

i want to know this RawNews has any image or not
please help

Sachin.Nand

2937 Posts

Posted - 2011-10-19 : 03:53:51

Join the tables and group by all the columns which are present in the select list except ImageId.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:18:18

[code]
SELECT r.NewsID,r.Title,r.Text,a.PropertyValuesString,i.ImgCnt
FROM aspnet_Profile a
JOIN RawNews r
ON r.UserID = a.UserID
JOIN(SELECT COUNT(ImageID) AS ImgCnt,NewsID
FROM Images
GROUP BY NewsID)i
ON i.NewsID = r.NewsID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2011-10-19 : 05:35:55

thanks
it works
but it doesn't overhead on the server when we are using lots of group by ?

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2011-10-19 : 05:37:22

visakh16
thanks
it must has better performance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:39:06

wc
check the execution plan and see query cost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources