- Joined
- Jun 11, 2003
- Messages
- 4,915
- Reaction score
- 68
- Points
- 48
- Location
- Mount Vernon, WA
- Your Mac's Specs
- MacBook Pro 2.6 GHz Core 2 Duo 4GB RAM OS 10.5.2
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
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