469,344 Members | 6,209 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Who DIDN'T vote - need solution

Our local club has about 15 Board members, and we vote online using a mySQL/php web interface. I have several different queries/results that I print which show varying displays of motions/votes/tallies and so on. NOW our Board wants to not only see the summary listing for all motions, but also include a list of each Board member who DIDN'T vote on any motion. I've tried various joins, COUNT, etc. but so far I haven't been able to add the names of non-voting Board members to the summary list. Here are the three tables that track this data:

TABLE 1 - BOARDINFO
Name
Board Member
(plus more fields with more detail about each Board Member)
PRIMARY KEY is Position/Year/Name

TABLE 2 - MOTIONS
MotionID
Result
(plus more fields that have text of each motion, dates, proposers, etc.)
PRIMARY KEY is MotionID

TABLE 3 - VOTES
MotionID
BoardMember
Vote (enum YES, NO, ABSTAIN, etc.)
PRIMARY KEY is MotionID/BoardMember

Currently my code produces a webpage with this info:
-------------------------------------------

The PEOPLE'S CHOICE CLUB
Board Motions Summary

Vote

20070826-45-E - VP - Mary Molloy - Carried
20070825-44-E - Maximum length of Events - Carried
20070825-42-E - Events dedications - Carried
20070807-41-E - Approve printing of Policy Manual - Carried
20070806-40-E - Events for 2008 - Carried
20070806-39-E - Use phone for soliciting memberships - Carried
20070730-38-E - Change to Ballot format - Carried
20070723-38-E - Buy new tape recorder - Carried
20070722-37-E - Pay for photos of Board members for publication - Lacked Majority
20070715-36-E - Publish photos of Board members - Carried

etc.
-----------------------------------------------------------------

What I'm also trying to achieve is the addition of the following (or something similar):

MOTION ID DIDN'T VOTE

20070826-45-E - VP - Mary Molloy - Carried
Didn't Vote: John Doe
Jane Smith
Tim Tam
20070825-44-E - Maximum length of Events - Carried
Didn't Vote: Mike Walsh
Ed Baker
20070825-42-E - Events dedications - Carried
Didn't Vote: John Doe
Ed Baker
etc.

Once I get the code to properly select the non-voters, I can work on the php to do the web display, but right now I'm stumped.

I look forward to a KISS solution :-)

Thanks!
Sep 5 '07 #1
1 1136
r035198x
13,262 8TB
Our local club has about 15 Board members, and we vote online using a mySQL/php web interface. I have several different queries/results that I print which show varying displays of motions/votes/tallies and so on. NOW our Board wants to not only see the summary listing for all motions, but also include a list of each Board member who DIDN'T vote on any motion. I've tried various joins, COUNT, etc. but so far I haven't been able to add the names of non-voting Board members to the summary list. Here are the three tables that track this data:

TABLE 1 - BOARDINFO
Name
Board Member
(plus more fields with more detail about each Board Member)
PRIMARY KEY is Position/Year/Name

TABLE 2 - MOTIONS
MotionID
Result
(plus more fields that have text of each motion, dates, proposers, etc.)
PRIMARY KEY is MotionID

TABLE 3 - VOTES
MotionID
BoardMember
Vote (enum YES, NO, ABSTAIN, etc.)
PRIMARY KEY is MotionID/BoardMember

Currently my code produces a webpage with this info:
-------------------------------------------

The PEOPLE'S CHOICE CLUB
Board Motions Summary

Vote

20070826-45-E - VP - Mary Molloy - Carried
20070825-44-E - Maximum length of Events - Carried
20070825-42-E - Events dedications - Carried
20070807-41-E - Approve printing of Policy Manual - Carried
20070806-40-E - Events for 2008 - Carried
20070806-39-E - Use phone for soliciting memberships - Carried
20070730-38-E - Change to Ballot format - Carried
20070723-38-E - Buy new tape recorder - Carried
20070722-37-E - Pay for photos of Board members for publication - Lacked Majority
20070715-36-E - Publish photos of Board members - Carried

etc.
-----------------------------------------------------------------

What I'm also trying to achieve is the addition of the following (or something similar):

MOTION ID DIDN'T VOTE

20070826-45-E - VP - Mary Molloy - Carried
Didn't Vote: John Doe
Jane Smith
Tim Tam
20070825-44-E - Maximum length of Events - Carried
Didn't Vote: Mike Walsh
Ed Baker
20070825-42-E - Events dedications - Carried
Didn't Vote: John Doe
Ed Baker
etc.

Once I get the code to properly select the non-voters, I can work on the php to do the web display, but right now I'm stumped.

I look forward to a KISS solution :-)

Thanks!
If you don't create an entry for those who didn't vote, then you could do
Expand|Select|Wrap|Line Numbers
  1. select  BoardMember                 from BOARDINFO where BoardMember not in select BoardMember from VOTES
If you create an entry in VOTES for each member that did not vote and set that value to say, 'NOVOTE' , then you can do

Expand|Select|Wrap|Line Numbers
  1. select  BoardMember                 from BOARDINFO where BoardMember in (select BoardMember from VOTES where vote = 'NOVOTE')
Sep 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Otis Green | last post: by
8 posts views Thread by William Drew | last post: by
14 posts views Thread by Otis Green | last post: by
1 post views Thread by Denis Van der Heyden | last post: by
18 posts views Thread by Urs Eichmann | last post: by
6 posts views Thread by Showjumper | last post: by
1 post views Thread by vote4amin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.