• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Best Way to Exclude Results Based on Unrelated Query

 
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nevermind... Figured it out using a select clause in the where. The solution I came up with was...



 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic