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