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
Solved

Creating a watch list with ASP and SQL

Posted on 2010-08-30
4
409 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
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Varible Table 3 32
Hide Table in merge 3 32
Html CheckBox obtain Its Value 5 28
XML and version of MS XML Dom Document versions 5 13
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…

837 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