SQL Query Statement Help

[triphis]

Hi!

 

This is what I have: a vote tabulation system that has ballots entered twice to ensure correctness. Then, if the program finds any discrepancies between the 1st or 2nd entries, it will force a third verification. We assume that the ballotID is always entered correctly, and it is used to drive this verification process.

 

My table: Columns(id, ballotID, politicianID, choice, entry)  [all are ints]

 

Let us pretend that I am only tabulating 50 votes. There will be 50 rows with entry=1. 50 rows with entry=2. And then some smaller number (say 5) of entry=3. So, for 5 of those votes, there will be 3 rows with the same ballotID. For any ballotIDs that DO NOT have a entry=3 row, then entry=1 and entry=2 are EQUIVALENT.

 

The entry=3 rows are the most correct, and trump their ballotID equivalents with entry=1 and entry=2.

 

I now need to select a "full set" of ballots. Where possible choosing the entry=3 equivalent, and where not possible, either the 1st or 2nd entry (since they are the same).

 

I regret that this is part of a group project and we are too far into development to change the tables and other setups now... so I ask that you limit your suggestions to how I can deal with this situation, assuming I can't change anything else.

 

Thank you for any help you can offer

[triphis]

Anyone?

[aschk]

Give us some time to reply, we're not on here 24/7 

 

You need a self JOIN, something like the following:

SELECT t1.ballot
      ,t1.politicianID
      ,t1.choice
      ,GREATEST(t1.entry,t2.entry) as 'entry'
FROM ballots t1
JOIN ballots t2 ON t1.ballot = t2.ballot
AND t1.politicianID = t2.politicianID
AND t1.choice = t2.choice
AND t1.id != t2.id
GROUP BY t1.ballot, t1.politicianID, t1.choice, entry

 

make sure to change "ballots" in the above to whatever your table name is.

[triphis]

Lol sorry. I always get impatient @_@

 

But that is exactly what I needed! Thank you SO much