help with query design / application design

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

SQL Server Development (2000)

help with query design / application design

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-13 : 05:39:05

I'm working on a query thats fairly complex for me, in fact I think its going to be a bit difficult to explain but I will do my best here. I'm also having problems on deciding in what format is the best way to bring back the data. (1 big query with all the data, or 1 query that lists all the records I should loop thru - this would make it easier to work with on the asp.net front end) I know its never good to have queries in a loop, and I don't on anything I have .. just maybe this scenario would make it easier?

I have 3 tables, the main being a table called "tblPhoto_Notifications". Users in "tblUserDetails" can subscribe to other members updates by having a row in "tblPhoto_Notifications".

For each user that has subscriptions I want to select this data where photos have been added in the past 24 hours, so that I can loop thru it and notify them. I'm not really sure the best way of bringing this data back, but this is what I need from the following tables.

tblextraphotos EP
tblphoto_notifications PN

pn.userID, pn.friendID, (count of photos from tblphoto_notifications WHERE pn.friendID = EP.userID) AND ep.photoDate > ep.dateadd(dd, -1, getdate())

This data above can have just 1 row for each userID with the total count of friendID records, then I can do a seperate select afterwards while looping thru these records. Or I could bring back multiple rows for each userID, which I think is sort of redundant ?

If this makes sense please let me know. :) I'd love to post more info after someone understands me, I dont want to over complicate things with more info now. I think this is a good general idea of what I am trying to do.

Thanks very much for any help!! any application design tips, guidance, suggestions, or query help is much appreciated!!!!!!

mike123

CREATE TABLE [dbo].[tblPhoto_Notifications] (
[userID] [int] NOT NULL ,
[friendID] [int] NOT NULL ,
[dateAdded] [smalldatetime] 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
) 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

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-13 : 23:56:13

"For each user that has subscriptions I want to select this data where photos have been added in the past 24 hours, so that I can loop thru it and notify them. I'm not really sure the best way of bringing this data back, but this is what I need from the following tables. "

Some piece is missing like what is in the tblExtraPhotos? an additional request? "For each user" refers to the UserID that has a record in either tblPhoto_Notifications or tblExtraPhotos or just the tblPhoto_Notification? What do you mean by "bringing this data" exactly?

May the Almighty God bless us all!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-14 : 05:04:51

Hi cmdr_skywalker,

Sorry I wasnt as clear as I could be .. I'll do my best here.

When users add photos, 1 row is added to tblextraphotos for each photo they add. This photo has a photoDate column.

If user 100, subscribes to user 500's photos I want to select the count of user 500's new photos agaisnt the photoDate. Let's say user 400 adds 5 photos, and user 600 adds 2 photos within the past 24 hours.

User 100 subscribes to both user 500's and user 600's updates

The data brought back would be like
userID/friendID/countNewPics

100 / 500 / 5
100 / 600 / 2

Does this make sense ?

Thanks very much once again!!
mike123

vallis
Starting Member

12 Posts

Posted - 2006-06-14 : 06:29:06

SELECT pn.userID, pn.friendID, COUNT(ep.counterID) AS newPhotos FROM tblPhoto_Notifications pn INNER JOIN tblExtraPhotos ep ON pn.friendID = ep.userID WHERE ep.photoDate >= DATEADD(d, -1, getdate()) GROUP BY pn.userID, pn.friendID

Is that the kind of thing you are after? If I understand you correctly that should do what you want.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-15 : 04:24:34

Sorry but been busy. I think, what vallis provided is the one you need. When you said "subscribes", we are understanding that it means tblPhoto_Notification.friendID = tblExtraPhotos.userID
* Note the equal sign in the


ep.photoDate >= DATEADD(d, -1, getdate())

Going back to your original inquiry:

quote:
This data above can have just 1 row for each userID with the total count of friendID records, then I can do a seperate select afterwards while looping thru these records.

You can also change the select query to return the total count of friend id records like this:


SELECT pn.userID, COUNT(DISTINCT pn.friendID), COUNT(ep.counterID) AS newPhotos FROM tblPhoto_Notifications pn INNER JOIN tblExtraPhotos ep ON pn.friendID = ep.userID WHERE ep.photoDate >= DATEADD(d, -1, getdate()) GROUP BY pn.userID

Hope this helps.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources