Help - MySQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
michaeru
Forum Commoner
Posts: 28
Joined: Sun Mar 07, 2010 5:22 pm

Help - MySQL Query

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help - MySQL Query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply