07-13-2007, 03:15 AM

Ok guys, i just can't figure this one out. Ill give you the scenario... and hopefully someone can figure this one out for me.. it's got to be simple, but my brain isn't working apparently

We have a contests table, a entries table and a voting table

When a person submits a contest, then people can submit entries to that contest, so the index relationship would be contest.id to entry.contest_id and then the contest people can vote on the entry to their contest, so

contest.id connects to entry.contest_id and entry.id connects to vote.entry_id

Now it's easy to figure out how many people have responded to a contest:

select count(*) from entries where contest_id = '1'

But now how do I figure how many entries he has that he has not voted on? especially when others can vote on it, so it's not like if I did a left join there would be null values for the vote, there could be multiple matches, but none with that user's id.. I hope that makes sense.. so for example:

select * from entries e left join votes v where e.contest_id = '1'

if there are no votes then it will return the entries that have not been voted on and the votes values would all be null values.

But if there are votes for that entry by other people besides the person that created the contest, then it will return values for the votes, when what I need is just to find out the total number of entries to their contest that they have not voted on yet.

Any ideas? I would hate to do multiple queries

07-13-2007, 03:22 AM

You need to query the voting table - for each person, get the number of votes grouped by entry_id - you should get 0 or 1 for each of them. of the top of my head:

select count(*) from votes group by entry_id where person=<whatever>

That syntax is probably wrong, I usually use Access to build queries like this, but that should do what you're after.

07-13-2007, 05:45 AM

Yeah I can't do it that way since that would be a massive amount of queries and I would like to do this in one fell swoop. For every contest there are many entries, for every entry there are several votes. So for one contest, I need to find out how many of the entries have I voted on already. And if I understand what you wrote correctly, I would need do a query to return all the entries and then do a query on each of the entries to find out if I have voted for that entry. If you have several thousand entries, then that's some serious overhead.. again if I understand you correctly

07-13-2007, 06:14 AM

I am a little confused as to what you are asking, but I was thinking something like:
select count(*) from entry e where
not exists (select * from vote v where e.id = v.entryid)
and e.contest_id = '1'

07-13-2007, 08:18 AM

Originally Posted by Murlyn

Yeah I can't do it that way since that would be a massive amount of queries and I would like to do this in one fell swoop. For every contest there are many entries, for every entry there are several votes. So for one contest, I need to find out how many of the entries have I voted on already. And if I understand what you wrote correctly, I would need do a query to return all the entries and then do a query on each of the entries to find out if I have voted for that entry. If you have several thousand entries, then that's some serious overhead.. again if I understand you correctly

The query I suggested can be embedded in another query to work for all contests, you select each contest and the count of the query I gave, similar to (this is wrong):

select contest.*, count(<my query>) from contest

I'm sure you can do it, you just need to nest a few queries. You might even need to create a View (or whatever MySQLs equivalent it) and query that as an intermediate step.