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:317843
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
