1

I have a table called student and it has the following fields:

idno(primary), lastname, status, password, syearid(foreign)

My problem is I have to store the unvoted students in each school_year(syearid):

sample

I have a table that stores students who have already voted. How should I track the unvoted students? Should I make another table?. One of my problems is when the student registers in school_year (2015) or syearid (2000) and votes it will create a student vote record, when student again votes for the next school_year it will create another record in the student votes table but my question is how to store an unvoted student if the student can register only once in a life time (primary is my idno so student can no longer register again but the school_year of the student is the same when she/he registered). What should I do?

Brendan Abel
  • 35,343
  • 14
  • 88
  • 118
Camille Fabre Valerio
  • 13
  • 5
  • 1
    multiple columns primary key? Add a 0/1 column for voted/not voted? I don't really understand what you wrote though. – Sebas Jan 07 '16 at 01:36
  • the school year is my problem when student registered like 2015 and voted from that year, it will save to other table that will handle the candid,idno,syearid , syearid will handle that student already voted, and that's not a problem cause it has a table that will handle student already voted but I have no records for the student who didn't vote a year – Camille Fabre Valerio Jan 07 '16 at 01:40

1 Answers1

0

You shouldn't need to explicitly store records of students that didn't vote for a given year. You can simply join students to the vote table and check for null. Students without vote records for a given year will not have voted. I'm assuming the syearid in the student votes table is the voting year and syearid in the students table is the year the student started school.

SELECT s.* FROM student s
LEFT JOIN student_votes sv ON (s.idno = sv.idno AND sv.syearid = 2015)
WHERE sv.syearid IS NULL

Also, assuming only students from certain years are eligible to vote

SELECT s.* FROM student s
LEFT JOIN student_votes sv ON (s.idno = sv.idno AND sv.syearid = 2015)
WHERE sv.syearid IS NULL 
AND s.syearid IN (2012, 2013, 2014, 2015)
Brendan Abel
  • 35,343
  • 14
  • 88
  • 118