wood burning stoves 2.0*
The moose likes JDBC and the fly likes Best Way to Exclude Results Based on Unrelated Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Best Way to Exclude Results Based on Unrelated Query" Watch "Best Way to Exclude Results Based on Unrelated Query" New topic
Author

Best Way to Exclude Results Based on Unrelated Query

Al Johnston
Ranch Hand

Joined: May 02, 2009
Posts: 99

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

Joined: May 02, 2009
Posts: 99

Nevermind... Figured it out using a select clause in the where. The solution I came up with was...



 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Best Way to Exclude Results Based on Unrelated Query
 
Similar Threads
Query Works in SQL, but Not in Java? [SOLVED]
Best Way to Turn Query to String[][]
simple inner join question
Example of Recursive to Iterative
Writing the results of this ResultSet to File is VERY slow (SOLVED)