Creating a watch list with ASP and SQL

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.

BestAviationAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
BestAviationAuthor Commented:
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
 
BestAviationAuthor Commented:
Was missing the vital WHERE EXISTS but it got me on the right idea of using a sub-query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.