This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Best Way to Exclude Results Based on Unrelated Query

 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyone,

We have created an ad serving solution (can be see at www.mmoabc.com if you create a free account and click on "Rewards"). When a user completes an offer, the system should not show that offer to the user again. Unfortunately, I have to work in ColdFusion for this process.

My problem is that I don't know how to do a join that will exclude records from the resultset and CF does not have an easy way to do this. I considered writing a java object that would do it for me and pass the ResultSet back to CF but ResultSet will remove the record from the underlying DB if I call deleteRow(). I need some way to remove items from the ResultSet when the user has completed an action.

Consider the following query:



That returns all offers regardless of whether the user has completed the offer or not. Now I need to exclude the offers the user has already done. I get those offers from a transaction table...



That returns all the offers that the user has already done. Now I want to remove those offerID's from the first query and display my results. In CF I can create a query of a query if I want to loop the original query and exclude all instances of the offerID in the second query. That's expensive!

Is there some sort of JOIN I can do for the original query that will exclude any offerID's from the CPA_Offer table that exist in the CPA_TransLogIFrame table that this PubUserID has completed?

Best,
Al
 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nevermind... Figured it out using a select clause in the where. The solution I came up with was...



 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic