Very tricky 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)

Very tricky query

Author

Topic

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-14 : 08:49:23

I have 4 tables
1.table_users
2.table_usercontent
3.table_purchasedcontent
4.table_contentincard.

table_users has many column but main are "createdby and expirydate"

I have to fatch the email ids of the user(s) who created the content,who already purchased the content.and who added to mycard but still not purchased.

In table_usercontent i have column as Expirydate.if the content is going to expire with in a week then i have to send mail to user who created that,who purchased and who added to card.

table_usercontent has
1.ContentId
2.Expirydate
3.Createdby

table_purchasedcontent has
1.UserId (who purchased the content)
2.ContentId

3.table_contentincard has
1.ContentId
2.UserId

First i have to check which content is going to expire in a week,i m doing like this

select createdby,ContentId from tbl_content where Expirydate between getdate() and DateAdd(wk,1,getdate())

now i have to select the user(s) who created,Purchased and added to Mycart ,get their email Ids and then loop that through cursor and send mail using their email id from Sql Server.

Can it be possible in Single Query.

any help will be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 08:51:44

You can do everything in one query, except for the actual email-sending operation.

E 12°55'05.25"
N 56°04'39.16"

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-14 : 08:55:30

what i want to do is use cursors and loop all selected rows and in each fetch query send a mail.
If i can get sample query that will really benifet me. or at least idea how can i proceed.

Regards

svicky9
Posting Yak Master

232 Posts

Posted - 2008-07-14 : 12:15:33

you can also send the emal using the xp_sendmail in Sql server 2000 and sp_send_dbmail in Sql Server 2005. Use the Query Option.

Vic

http://vicdba.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources