help with 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)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-29 : 03:19:01

Hi,

I have the following tables below and I am trying to contruct a query that shows the most popular pics. Most popular pics (counterID) are determined by the count of counterID in the tblFavouritePhotos table.

I want to return the following columns

tblFavouritePhotos.userID
tblFavouritePhotos.counterID
tblExtraPhotos.caption
tblUserDetails.nameOnline

We ca join the tables the following way

JOIN tblFavouritePhotos FP on tblExtraPhotos.counterID = FP.counterID
JOIN tblUserDetails UD on FP.userID=UD.userID

Am I providing enough info? And does this make sense? I can't seem to figure out how to piece all the parts together. I have done similar with just 1 table, but 2 tables is really confusing me.

Thanks very much once again!
mike123

CREATE TABLE [dbo].[tblFavouritePhotos] (
[favID] [int] IDENTITY (1, 1) NOT NULL ,
[userID] [int] NOT NULL ,
[counterID] [int] NOT NULL ,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblExtraPhotos] (
[counterID] [int] IDENTITY (1, 1) NOT NULL ,
[photoID] [tinyint] NOT NULL ,
[userID] [int] NOT NULL ,
[photoDate] [smalldatetime] NOT NULL ,
[caption] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 03:47:52

select counterid, count(*)
from tblfavouritephotos
group by counterid
order by 2 desc

Peter Larsson
Helsingborg, Sweden