?
Solved

Creating a watch list with ASP and SQL

Posted on 2010-08-30
4
Medium Priority
?
413 Views
Last Modified: 2012-05-10
I'm trying to create a "watch" list where members of my site can add articles/jobs/etc. to their short-list. However, I'm struggling which approach to take on this.

I could create a watch-list table with a user_id and article_id and create a new record for each article a user ads to his/her watch list. Then loop through the table and include all the article_id's in an SQL IN statement executed on the article table.

So something like this:

Step 1:
set rs = ObjCn.excute("SELECT * FROM watch_list WHERE user_id='123'")
Do while not rs.eof
article_ids = article_ids & rs("article_id") & ","
rs.movenext : LOOP
rs.close : set rs = nothing

Step 2:
set rs = ObjCn.execute("SELECT * FROM article_table WHERE SQL IN ("&article_ids&")")
do while not rs.eof
rs("article_name") & "<br />"
rs.movenext : loop
rs.close : set rs = nothing

The problem I have with this method though is that I have to execute the SQL database twice for each list I create.

Does anybody have a more efficient method of creating a subscriber list that I can use?

Any help would be greatly appreciated!

PS: the code above is for illustration of method only so it would trow up errors if there were no records in any of the tables etc... just mentioning it before someone starts correcting the code itself.

0
Comment
Question by:BestAviation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 33557429
you could do this:

set rs = ObjCn.execute("SELECT * FROM article_table WHERE SQL IN (select article_id  FROM watch_list WHERE user_id='123')")

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33557435
0
 

Author Comment

by:BestAviation
ID: 33581695
Hmm....

set rs = ObjCn.execute("SELECT * FROM article_table WHERE SQL IN (select article_id  FROM watch_list WHERE user_id='123')")

doesn't work. You have to include a WHERE EXISTS to use a sub query I found out.

Problem solved, and just in case someone else will use this question for their own research, the answer would be like this

set rs = ObjCn.execute("SELECT * FROM article_table WHERE EXISTS (SELECT article_id  FROM watch_list WHERE watch_list.article_id=article_table.id AND watch_list.user_id='123')")

Thanks for your help.
0
 

Author Closing Comment

by:BestAviation
ID: 33581709
Was missing the vital WHERE EXISTS but it got me on the right idea of using a sub-query.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question