Cheers Dom. I read that NOT EXISTS did little or nothing for performance in this case. Of course I can't point you to where I read that!
Anyone know for sure? Adrian > -----Original Message----- > From: Dominic Watson [mailto:[email protected]] > Sent: 13 January 2009 13:48 > To: cf-talk > Subject: Re: need some database advice > > >SELECT TOP 1 itemID > >FROM item > >WHERE itemID NOT IN ( > > SELECT itemID > > FROM vote > > WHERE userID = theUserVoting > >) > > That doesn't work? Should work fine, as should NOT EXISTS: > > SELECT TOP 1 i.itemID > FROM item i > WHERE NOT EXISTS ( > SELECT v.itemID > FROM vote v > WHERE v.itemId = i.itemId > AND v.userID = theUserVoting > ) > > Dom > > > > 2009/1/13 Adrian Lynch <[email protected]>: > > I'm doing the same but with voting. People get to vote once per item > and I > > store this so they can't vote again. > > > > I'm doing something like: > > > > SELECT vote > > FROM vote > > WHERE userID = theUserVoting > > AND itemID = theItemTheyreAttemptingToVoteFor > > > > This runs a lot and so far so good. > > > > What I AM having problems with is getting a random item that they've > yet to > > vote for. > > > > I have this: > > > > SELECT TOP 1 itemID > > FROM item > > WHERE itemID NOT IN ( > > SELECT itemID > > FROM vote > > WHERE userID = theUserVoting > > ) > > > > I've read around a bit and EXISTS might be the way forward but the > examples > > I've read use IN and not NOT IN. > > > > Any ideas? > > > > Sorry for the half-a-thread hijack! > > > > Adrian > > > >> -----Original Message----- > >> From: Mike Soultanian [mailto:[email protected]] > >> Sent: 13 January 2009 07:37 > >> To: cf-talk > >> Subject: need some database advice > >> > >> Hey Everyone, > >> I have a project and I'm trying to figure out the best way to go > about > >> it. What I want to do is keep track of what songs a user has > listened > >> to and what songs they haven't. The first thing that comes to mind > is > >> a > >> table with song IDs and a table with user IDs and a join table > between > >> the two that keeps track of what song a user has listened to. With > >> 50,000 songs, that could be a lot of records in the join table. Is > >> there a more efficient way to tackle this kind of problem? I don't > >> think I'll have that many users, but even if I had ten users, that > >> table > >> could be pretty big. > >> > >> I'm trying to think if there are any tricks such as whether to store > if > >> a user has listened to a track or store if they haven't. I plan to > >> have > >> a button called "mark all as listened", which could empty the join > >> table > >> of any records pertaining to that user if I was storing the tracks > they > >> didn't listen to. So the join table would initially start out very > >> large for a user and then drop down... that's just one thought I > had. > >> > >> If anyone has any tricks, I'd appreciate your advise! > >> > >> Thanks, > >> Mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317846 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
