Joining three tables

[padams]

I've had this problem in the past, and it could well be because of poor database organisation, but if there's a way I can work this out without having to re-do the database I'd be grateful!

 

Currently I am using:

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 ORDER BY gamesplayed DESC

 

This gets the player names from the players table, then counts up how many games they appeared in from the games table, then joins the results together. This all works fine. However, I would also like to count up how many tries they have scored from a third table, called tries. The tries table has a playerID field in each record, so I can count them up as I have been doing with games.

 

I have used a second query, much like the one above, to get the try scoring records, but can not work out how to combine the two queries into one. Any ideas?

 

If needed, the try-scoring query is:

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 ORDER BY triesscored DESC

 

Thanks

[rhodesa]

try this, not sure if it will work though:

 

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

[padams]

Some kind of progress I think. It returned the player names and set up gamesplayed and triesscored. But for the gamesplayed and triesscored it has returned identical amounts, as if they are counting the same things. Also, the counts are very high numbers and don't match the actual data in the tables. For example, playerID 1 had 4964 as their gamesplayed and triesscored, but in actual fact had only played 73 games and scored a similar number of tries.

[rhodesa]

can you dump the tables/data to an SQL file so i can test some different queries? either post it as an attachment here or PM/email it to me.

[fenway]

Post INSERT statements here... don't take this topic offline.

[padams]

Here's the SQL to create the three tables and insert a few records. There will be 2 games played, with a few players involved and tries scored.

 

CREATE TABLE `games` (
  `gameID` int(7) NOT NULL auto_increment,
  `matchID` int(5) NOT NULL,
  `playerID` varchar(50) NOT NULL,
  `oppositionID` varchar(50) NOT NULL,
  `gameSeason` varchar(10) NOT NULL,
  `teamID` varchar(50) NOT NULL,
  PRIMARY KEY  (`gameID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1231 ;

INSERT INTO `games` (`gameID`, `matchID`, `playerID`, `oppositionID`, `gameSeason`, `teamID`) VALUES 
(1, 1, '1', 'Yeehaa', '2002', '1'),
(2, 1, '4', 'Yeehaa', '2002', '1'),
(3, 1, '5', 'Yeehaa', '2002', '1'),
(4, 1, '7', 'Yeehaa', '2002', '1'),
(5, 1, '11', 'Yeehaa', '2002', '1'),
(6, 1, '10', 'Yeehaa', '2002', '1'),
(7, 1, '9', 'Yeehaa', '2002', '1'),
(8, 1, '2', 'Yeehaa', '2002', '1'),
(9, 1, '8', 'Yeehaa', '2002', '1'),
(10, 1, '6', 'Yeehaa', '2002', '1'),
(11, 2, '1', 'Yeehaa', '2002', '1'),
(12, 2, '4', 'Yeehaa', '2002', '1'),
(13, 2, '12', 'Yeehaa', '2002', '1'),
(14, 2, '10', 'Yeehaa', '2002', '1'),
(15, 2, '9', 'Yeehaa', '2002', '1'),
(16, 2, '2', 'Yeehaa', '2002', '1'),
(17, 2, '3', 'Yeehaa', '2002', '1');

CREATE TABLE `players` (
  `playerID` int(3) NOT NULL auto_increment,
  `playerFirstName` text NOT NULL,
  `playerLastName` text NOT NULL,
  `playerTeams` text NOT NULL,
  `playerPhoto` text NOT NULL,
  `playerAbout` text NOT NULL,
  PRIMARY KEY  (`playerID`),
  FULLTEXT KEY `playerFirstName` (`playerFirstName`),
  FULLTEXT KEY `playerLastName` (`playerLastName`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=122 ;

INSERT INTO `players` (`playerID`, `playerFirstName`, `playerLastName`, `playerTeams`, `playerPhoto`, `playerAbout`) VALUES 
(1, 'Phil', 'Adams', 'Mens, Red, Singapore, Grey', 'philadams.jpg', ''),
(2, 'Mark', 'Ludemann', 'Mens, Red, Singapore, Grey', 'markludemann.jpg', ''),
(3, 'Andrew', 'Marsh', 'Mens, Grey, Singapore', 'andrewmarsh.jpg', ''),
(4, 'Raechel', 'Adams', 'Mens, Red', 'raecheladams.jpg', ''),
(5, 'Darryn', 'Collins', 'Mens, Grey, Red, Singapore', 'darryncollins.jpg', ''),
(6, 'Nick', 'Ryan', 'Mens, Red, Grey, Singapore', 'nickryan.jpg', ''),
(7, 'Justin', 'Conway', 'Mens, Singapore, Wellington Mixed, London Mens, London Mixed', 'justinconway.jpg', ''),
(8, 'Mark', 'Rippin', 'Mens, Red, Grey', 'markrippin.jpg', ''),
(9, 'Leyton', 'Hunt', 'Mens, Red, Grey', 'leytonhunt.jpg', ''),
(10, 'Andrew', 'Hawkes', 'Mens, Red, London Mens, London Mixed', 'andrewhawkes.jpg', ''),
(11, 'Chris', 'Goldsbrough', 'Mens, London Mens, London Mixed', 'chrisgoldsbrough.jpg', ''),
(12, 'Nick', 'Adams', 'Mens', '', '');

CREATE TABLE `tries` (
  `tryID` int(5) NOT NULL auto_increment,
  `matchID` int(5) NOT NULL,
  `opponentID` varchar(20) NOT NULL,
  `tryTeam` varchar(15) NOT NULL default '',
  `trySeason` int(9) NOT NULL default '0',
  `playerID` int(4) NOT NULL default '0',
  PRIMARY KEY  (`tryID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=547 ;

INSERT INTO `tries` (`tryID`, `matchID`, `opponentID`, `tryTeam`, `trySeason`, `playerID`) VALUES 
(1, 1, 'Yeehaa', '1', 2002, 1),
(2, 1, 'Yeehaa', '1', 2002, 9),
(3, 1, 'Yeehaa', '1', 2002, 9),
(4, 1, 'Yeehaa', '1', 2002, ,
(5, 1, 'Yeehaa', '1', 2002, 6),
(6, 1, 'Yeehaa', '1', 2002, 6),
(7, 2, 'Yeehaa', '1', 2002, 1),
(8, 2, 'Yeehaa', '1', 2002, 1),
(9, 2, 'Yeehaa', '1', 2002, 1),
(10, 2, 'Yeehaa', '1', 2002, 9),
(11, 2, 'Yeehaa', '1', 2002, 13),
(12, 2, 'Yeehaa', '1', 2002, ;

[rhodesa]

i don't think it can be done in one query. is there a reason it can't be two?

[fenway]

You'll likely need to use a subquery.

[mrmitch]

I think the first statement was close, but the second left join needed clarifying (if this will run in MySQL)

 

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

[Barand]

try

mysql> SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`,
    -> g.gamescount, t.trycount
    -> FROM players p
    -> LEFT JOIN
    ->   (
    ->     SELECT playerID, COUNT(*) as gamescount
    ->     FROM games
    ->     GROUP BY playerID
    ->   ) as g USING (playerID)
    -> LEFT JOIN
    ->   (
    ->     SELECT playerID, COUNT(*) as trycount
    ->     FROM tries
    ->     GROUP BY playerID
    ->   ) as t  USING (playerID)
    -> ;
+----------+-----------------+----------------+------------+----------+
| playerID | playerFirstName | playerLastName | gamescount | trycount |
+----------+-----------------+----------------+------------+----------+
|        1 | Phil            | Adams          |          2 |        4 |
|        2 | Mark            | Ludemann       |          2 |     NULL |
|        3 | Andrew          | Marsh          |          1 |     NULL |
|        4 | Raechel         | Adams          |          2 |     NULL |
|        5 | Darryn          | Collins        |          1 |     NULL |
|        6 | Nick            | Ryan           |          1 |        2 |
|        7 | Justin          | Conway         |          1 |     NULL |
|        8 | Mark            | Rippin         |          1 |        2 |
|        9 | Leyton          | Hunt           |          2 |        3 |
|       10 | Andrew          | Hawkes         |          2 |     NULL |
|       11 | Chris           | Goldsbrough    |          1 |     NULL |
|       12 | Nick            | Adams          |          1 |     NULL |
+----------+-----------------+----------------+------------+----------+

[Barand]

I see you are using TEXT types. Which of your players has a name 65000 chars long?

[padams]

Yes yes, mock all you want! This was the first database-driven site I ever put together so there are a few bugs...

 

Thank you so much for coming up with this query, a huge weight off my mind. Using the brackets has solved it perfectly, is that what was referred to as a subquery? I've never seen it done like that before.

 

Out of interest, is there any way to change all null values to a zero?

[rhodesa]

for each column you want to update:

 

UPDATE tableName SET colName = '0' WHERE colName IS NULL

[mrmitch]

After setting the NULL's to zero, you should give the column a default of 0. That'll stop it from ever having null values.

 

[Barand]

SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`,
    IFNULL(g.gamescount,0) as gamescount, 
    IFNULL(t.trycount,0) as trycount
FROM players p
  ...
  etc

[Barand]

After setting the NULL's to zero, you should give the column a default of 0. That'll stop it from ever having null values.

 

 

The nulls are the result of a LEFT JOIN where there are no matching rows in the right table/subquery, not values stored in a table.

[padams]

The ifnull bit works fine, thanks again.

 

I've had a go at then filtering the query. The one you have done for me is great for selecting all players in the club, but I'm not sure how to go about running a similar query where I filter the selected players so that only those who belong to a particular team appear. I tried ($team is the teamID that is selected by the user):

 

SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`, IFNULL(g.gamescount,0) as gamescount, IFNULL(t.trycount,0) as trycount FROM players p LEFT JOIN (SELECT playerID, COUNT(*) as gamescount FROM games WHERE games.teamID=$team GROUP BY playerID) as g USING (playerID) LEFT JOIN (SELECT playerID, COUNT(*) as trycount FROM tries WHERE tries.tryTeam=$team GROUP BY playerID) as t  USING (playerID) ORDER BY gamescount DESC

 

As I understand it, the LEFT JOIN selects all records? How would I filter this query so that only those records with at least 1 in the trycount column will be displayed?

[Barand]

try scorers only

 

SELECT p.`playerID`, p.`playerFirstName`, p.`playerLastName`,
IFNULL(g.gamescount,0) as games, IFNULL(t.trycount,0) as tries
FROM players p
INNER JOIN
  (
    SELECT playerID, COUNT(*) as trycount
    FROM tries
    GROUP BY playerID
  ) as t  USING (playerID)
LEFT JOIN
  (
    SELECT playerID, COUNT(*) as gamescount
    FROM games
    GROUP BY playerID
  ) as g USING (playerID)

+----------+-----------------+----------------+-------+-------+
| playerID | playerFirstName | playerLastName | games | tries |
+----------+-----------------+----------------+-------+-------+
|        1 | Phil            | Adams          |     2 |     4 |
|        6 | Nick            | Ryan           |     1 |     2 |
|        8 | Mark            | Rippin         |     1 |     2 |
|        9 | Leyton          | Hunt           |     2 |     3 |
+----------+-----------------+----------------+-------+-------+