Skip to main content
IrishNJ
IrishNJ
Inspiring
February 11, 2010
Answered

WHERE rowID NOT IN (another recordset on same page?)

  • February 11, 2010
  • 1 reply
  • 670 views

Hi.

I have a recordset that lists movie reviews.  On the same page, I have a menu that lists movies, for review.

I want to hide movies, in that list, if they already exist in the users recordset of reviews.

In other words, if the user has already submitted a movie review, I don't want them to be able to submit another review for the same movie.

As I understand it, I can use the NOT IN function in my SELECT statement for my movie recordset, but I'm not sure how to call the movieID from the review recordset, in this statement.

I'm aiming for something like this:

rsUserReviews

reviewID

movieID

rsMovies

movieID

In the movie recordset I need to do something like this:

SELECT movieID, moviename FROM dbo.movies WHERE movieID NOT IN (SELECT movieID FROM rsUserReviews)

Is this possible?

Appreciate any advice.  Thanks.
Nath.

This topic has been closed for replies.
Correct answer David_Powers

nathonjones wrote:

In the movie recordset I need to do something like this:

SELECT movieID, moviename FROM dbo.movies WHERE movieID NOT IN (SELECT movieID FROM rsUserReviews)

Is this possible?

No. You don't say which server-side technology you're using, but since there's no $ on rsUserReviews, I assume you're using ASP. Basically, the SELECT subquery in NOT IN() needs to use the same query as the other recordset.

1 reply

David_Powers
David_PowersCorrect answer
Inspiring
February 11, 2010

nathonjones wrote:

In the movie recordset I need to do something like this:

SELECT movieID, moviename FROM dbo.movies WHERE movieID NOT IN (SELECT movieID FROM rsUserReviews)

Is this possible?

No. You don't say which server-side technology you're using, but since there's no $ on rsUserReviews, I assume you're using ASP. Basically, the SELECT subquery in NOT IN() needs to use the same query as the other recordset.

IrishNJ
IrishNJAuthor
Inspiring
February 11, 2010

Hi David.  Sincere apologies.  That is a bad habit of mine.

I'm using Classic ASP / VBScript.

So you're suggesting:

SELECT movieID FROM dbo.movies WHERE movieID NOT IN (SELECT dbo.reviews.userID, dbo.reviews.movieID FROM dbo.reviews WHERE dbo.reviews.userID = ?)

Do I just add another parameter, in the movie recordset, to accommodate the userID reference?

When I try that, I get this:

[SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Hope you can help.  Thank you.

Regards
Nath.

IrishNJ
IrishNJAuthor
Inspiring
February 11, 2010

Ah, I've got it.

SELECT movieID FROM dbo.movies WHERE movieID NOT IN (SELECT dbo.reviews.movieID FROM dbo.reviews WHERE dbo.reviews.userID = ?)

I can, basically, only use on form field in the SELECT statement within the NOT IN ().

Appreciate the help David.  Thank you.

Regards
Nath.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices