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 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? 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. 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 Link to comment https://forums.phpfreaks.com/topic/98627-sql-query-statement-help/#findComment-505676 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.