IN problem

[padams]

I've run into a problem with the IN command. I'm trying to count the number of records that include a particular number in the field matchTeam. However, the matchTeam field is a text field containing a set of numbers all separated by commas. When I run a query (such as below) it doesn't return any results. How can I get around this?

 

SELECT COUNT(matchTeam) as games FROM matches WHERE matchTeam IN (2)

 

Sample matchTeam field is 1, 2, 3, 4, 6, 7, 31, 36

[fenway]

Why would you store a list in a single field... it's not a problem with the IN command, it's a problem with your table design.  Normalize it, and you'll be fine.

[padams]

The database is supporting a rugby team and their statistics. The table in question contains match data, including all of the players who played in each match. Because the number of players who play from week to week varies, I didn't think I could have a set number of fields (player1, player2, etc), so thought I could more easily include them all in one field.

 

The problem I have hit now, of course, is that when I want to query this table and show number of games played it doesn't seem possible. So I take it even if I had them as a list of names rather than integers, it wouldn't be possible to use IN?

 

Is there any way to combine GROUP BY and IN?

 

I may have to rethink the way I have set up the database otherwise.

[fenway]

You will have to rethink it... you aren't supposed to have a set number of fields, you're supposed to have another table that links players to teams to weeks.

[padams]

Okay, I'm trying a new approach. I have created a new table called games, in which every row represents a player playing a match. So, if 15 people play in a game, there will be 15 new records entered. I've got the PHP all working, creating records automatically when matches are created via a webform, but now comes the kicker - how do I pull the information out?

 

I have the following query running successfully already, that gets the player first and last names from the 'players' table, groups all tries scored by tryscorer and then counts each group, and joins them together. How could I add an additional join, where I also group all games by the player and count each group.

 

SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as num FROM tries

JOIN players ON tries.playerID = players.playerID GROUP BY tries.playerID

 

I started to add in the games.gamePlayer (that will match playerID in the players and tries table) but couldn't get anything to work. This is as far as I got:

SELECT tries.playerID, players.playerFirstName, players.playerLastName, games.gamePlayer, COUNT(tries.playerID) as num FROM tries, COUNT(games.gamePlayer) as gamesplayed FROM games JOIN players ON tries.playerID = players.playerID JOIN games ON players.playerID = games.gamePlayer GROUP BY tries.playerID GROUP BY games.gamePlayer

[Barand]

What is the table structure?

[pre]

game                            player

--------                        ----------

gameID    --+              +--  playerID

gamedate    |              |    Firstname

venue      |              |    Lastname

against    |              |

            |              |

            |              |

            |  team      |        tries   

            |  --------  |        ---------

            |  id        |        id     

            +-- gameID    |  -----  gameID 

                position  |    +--  playerID

                playerID --+  --+     

[padams]

I've changed a few field names as I didn't have them matching up so it wasn't totally transparent as to how they all related. The below structure sums it up.

 

game                                                          players

-----                                                          -------

gameID                +---------------------------playerID ---------------+

playerID -----------+                                    playerFirstName            |

opponentID  -----------------+                      playerLastName            |

gameSeason                        |                                                        |

teamID  ------------+            |                                                        |

                            |            |            opponents                              |

                            |            |          ----------                              |

                            |            +------ opponentID  -------------+        |

                            |                        opponentName              |        |

                            |                                                          |        |

                            |    team                      tries                  |        |

                            |      -----                      -----                |      |

                              +--teamID  ----------+    tryID                |      |

                                  teamOtter            |    opponentID  ---+      |

                                                            +---tryTeam                    |

                                                                  playerID---------------+

[Barand]

Haven't any data so not tested.

 

SELECT  g.playerID, p.playerFirstName, p.playerLastName,
        COUNT(DISTINCT g.gameID) as gamesplayed,
        COUNT (tr.playerID) as triesscored
FROM    game g
        INNER JOIN players p ON g.playerID = p.playerID
        INNER JOIN team t ON g.teamID = t.teamID
        LEFT JOIN tries tr ON t.teamID = tr.tryTeam
GROUP BY g.playerID, p.playerFirstName, p.playerLastName

 

Your normalisation looks a bit off. OpponentID crops up in too many places.

 

Perhaps ??

[pre]

game                                            players

-----                                          -------

playerID -------------------------------------  playerID ---------------+

opponentID  -----------------+                  playerFirstName        |

gameSeason                  |                  playerLastName          |

teamID  ------------+        |                                          |

gameID ----+        |        |          opponents                      |

          |        |        |          ----------                    |

          |        |        +---------- opponentID                    |

          |        |                    opponentName                  |

          |        |                                                  |

          |        |  team                    tries                    |

          |        |  -----                  -----                    |

          |        +--teamID                  tryID                    |

          |          teamOtter              tryTeam                  |

          |                                  playerID  ---------------+

          +---------------------------------  gameID

[/pre]

[padams]

I had opponent in the tries table so that I could see how many tries the team and each player had scored against any given team.

 

Returned no results, but no errors at least.

 

I don't understand how the query works. How can you have g.playerID rather than games.playerID? Same question with the p, t and tr throughout the query.

 

I may use left join for the players table so all players are listed. The final result should hopefully have all the players with their first and last names, then the total number of games they have played followed by how many tries each has scored. By left joining players I think that would ensure all players would be included, even those who haven't scored a try?

[padams]

I've been playing around with it some more using what you showed me and what I have been doing. I can get all of the games or all of the tries using the two separate queries below, but can't manage to combine them. Like I said, I didn't understand how your suggestion worked so wanted to keep trying.

 

Tries:

SELECT tries.playerID, players.playerFirstName, players.playerLastName, COUNT(tries.playerID) as triesScored,

FROM tries JOIN players ON tries.playerID = players.playerID GROUP BY tries.playerID

 

Games:

SELECT games.playerID, players.playerFirstName, players.playerLastName, COUNT(games.playerID) as gamesPlayed FROM games

JOIN players ON games.playerID = players.playerID GROUP BY games.playerID

[Barand]

Plan B. Let's try an alternative route through the joins

SELECT p.playerID, p.playerFirstName, p.playerLastName,
       COUNT(g.gameID) as gamesplayed,
       COUNT(tr.tryID) as triesscored
FROM players p
     LEFT JOIN game g ON p.playerID = g.playerID
     LEFT JOIN tries tr ON p.playerID = tr.playerID
GROUP BY p.playerID

 

PS p, g, tr are aliases for players, game, tries tables

 

...FROM players p

    LEFT JOIN game g ON p.playerID = g.playerID

    LEFT JOIN tries tr ON p.playerID = tr.playerID

 

As for opponentID, if you know the gameID then you know who the opponent is.

[padams]

Seems to be getting closer - I think. Still not working though, it returned something like 87 games for a player who had only played 2 games, and was incorrect on every single game. Same for tries, and I can't work out where the numbers have come from.