triphis Posted March 30, 2008 Share Posted March 30, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/98627-sql-query-statement-help/ Share on other sites More sharing options...
triphis Posted March 31, 2008 Author Share Posted March 31, 2008 Anyone? Quote Link to comment https://forums.phpfreaks.com/topic/98627-sql-query-statement-help/#findComment-505336 Share on other sites More sharing options...
aschk Posted March 31, 2008 Share Posted March 31, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/98627-sql-query-statement-help/#findComment-505499 Share on other sites More sharing options...
triphis Posted March 31, 2008 Author Share Posted March 31, 2008 Lol sorry. I always get impatient @_@ But that is exactly what I needed! Thank you SO much Quote Link to comment https://forums.phpfreaks.com/topic/98627-sql-query-statement-help/#findComment-505676 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.