Page 1 of 1

Help - MySQL Query

Posted: Tue Mar 09, 2010 6:38 am
by michaeru
I have made a simple automated election site.

I made 4 tables:
1. tblcandidates
- candidateID
- lname
- fname
- mname
- position
- description
- achievements
- votes
2. tblvoters
- voterID
- studentIDNumber
- code
3. tblvotcan
- votcanID
- voterID
- candidateID
4. tblaccounts
- accountID
- pcode1
- pcode2
- type

The 3rd table holds data on who voted who. I have a function that will output the candidates that have not been voted by the voter. But I have a problem on the query.

Here are the constrains:
- The voter can only vote a candidate once
- The site will only display the candidates that the current voter did not vote

Here is my current SQL query:

Code: Select all

 
SELECT *, `tblvotcan`.`candidateID` as cID1, `tblcandidates`.`candidateID` as cID2
FROM `tblcandidates`
LEFT JOIN `tblvotcan` 
ON (`tblcandidates`.`candidateID` = `tblvotcan`.`candidateID`)
WHERE (`tblvotcan`.`voterID` <> '[i]*voterID goes here*[/i]'
OR `tblvotcan`.`voterID` IS NULL)
AND `position` = '[i]*position value goes here*[/i]'
 
Sure it does do the needed query for the first voter. But the succeeding voters will not. I know that the problem is in the `tblvotcan`.`voterID` <> '*voterID goes here*'. Since the candidates that were voted by other voters will still be displayed.

Please help me achieve the needed query.

Re: Help - MySQL Query

Posted: Tue Mar 09, 2010 7:08 am
by VladSun
What's the purpose of `tblvotcan`.`candidateID` column - I think you don't need it.

You need to OUTER JOIN (e.g. LEFT JOIN) tables tblcandidates and tblvotcan. Then filter the results by voterID *and* NULL values for a `tblvotcan` column.