This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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?