Comparing Fields

[The Little Guy]

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.

[fenway]

1. Join the table to itself, and use this p1=p2 as the join condition.

2. I don't see users anywhere.

[The Little Guy]

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.

[fenway]

Then add this to the join condition.

[The Little Guy]

Then add this to the join condition.

 

Add what?

[fenway]

You can also check that the old table's p1 / p2 does not match the new one's.

[The Little Guy]

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?

[fenway]

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.