The Little Guy Posted January 4, 2008 Share Posted January 4, 2008 I have a table called eventTeamList with 5 columns: id, eventID, p1, p2, teamName. With this code: SELECT * FROM eventTeamList WHERE eventID = '%s' ORDER BY RAND() LIMIT 2 I need 2 things to happen 1. I need to select 2 teams with an equal number of players (p1 will always have a value, but p2 may not have a value). so... I need to select 2 teams either a 2v2 or 1v1, NOT a 1v2 or 2v1. 2. I need select teams where a user doesn't play his or her self, since the user can be on 2+ different teams. Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/ Share on other sites More sharing options...
fenway Posted January 4, 2008 Share Posted January 4, 2008 1. Join the table to itself, and use this p1=p2 as the join condition. 2. I don't see users anywhere. Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-430626 Share on other sites More sharing options...
The Little Guy Posted January 4, 2008 Author Share Posted January 4, 2008 2. I don't see users anywhere. an example table can look like this: id eventID p1 p2 teamName 1 2 ryan dog kitty1 2 2 ryan kitty2 3 2 mike ryan kitty3 4 2 joe billy kitty4 p1 can not play himself so ryan can not be selected where ryan plays himself in the table above joe and billy will always be playing, since ryan is on every other team, and ryan would be playing himself which can not happen. also id 2 will never get to play since there is no other 1 player team. Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-430639 Share on other sites More sharing options...
fenway Posted January 5, 2008 Share Posted January 5, 2008 Then add this to the join condition. Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-430921 Share on other sites More sharing options...
The Little Guy Posted January 5, 2008 Author Share Posted January 5, 2008 Then add this to the join condition. Add what? Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-431462 Share on other sites More sharing options...
fenway Posted January 5, 2008 Share Posted January 5, 2008 You can also check that the old table's p1 / p2 does not match the new one's. Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-431489 Share on other sites More sharing options...
The Little Guy Posted January 7, 2008 Author Share Posted January 7, 2008 I'm sorry, I don't follow, I have never done a query like this before. I don't see how checking if p1 = p2 would be valid to check if a player is playing his/herself. would It be better to do (to check that a user doesn't play his/herself): ...( old.p1 != new.p1 OR old.p2 != new.p2 OR old.p1 != new.p2 )... If that is what I could do... then How could I check for 1v1 or 2v2 tournaments and NOT 1v2 tournaments? Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-432250 Share on other sites More sharing options...
fenway Posted January 7, 2008 Share Posted January 7, 2008 Yes, that's what you'd need in the on clause... as for the same # of players, you'd need to check if p1 and/or p2 (possibly just p2 if your logic dictates that) is empty/null and add this expression to your on clause as well. Quote Link to comment https://forums.phpfreaks.com/topic/84521-comparing-fields/#findComment-432694 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.