Connecting Id field with name field

Question

Hi, Am having a table with quetion_id , nominees and vote_count. In which the values for question_id and nominees are prepopulated from other tables with vote_count as zero.

If the users select some nominees the vote count should be incresed by one. The problem is How to connect the question_id and nominees like for this question_id this nominee is selected .

can some one give example for this situation..

Answer 1

I'll answer based on this scenario:

So you have a...

1) User

who can...

2) Vote

for a...

3) Nominee

And it's a given that MANY users can vote for MANY nominees.

You probably aready have tblUser and tblNominee - so you need a link table that can contain the votes (tblUserNomineeVote).

tblUserNomineeVote has fields for UserId and NomineeId, and therefore registers a vote. You may need to add constraints depending on how many votes a user can register etc.

You can then use:

SELECT
   tblNominee.Name,
   COUNT(*)
FROM
   tblNominee
INNER JOIN
   tblUserNomineeVote ON tblUserNomnieeVote.NomineeId = tblNominee.NomineeId
GROUP BY
    tblNominee.Name